Excel VBA to Clear Contents of Named Range (3 Macro Variants)

The article will show you how to clear the contents of a Named Range in Excel VBA. If your dataset contains Named Ranges and you need to change the values in the Named Ranges, you should clear the contents of those Named Ranges first. Sometimes you may need to clear a specific Named Range, sometimes you may need to clear all the Named Ranges in your sheet. In this article, I’ll show you the path to salvation in this situation. Please stay tuned and go through the whole article.


Download Practice Workbook


3 VBA Variants to Clear Contents of Named Range in Excel

In this article, we have a dataset of information about some people. We will create some Named Ranges based on the data of this workbook and clear their contents.

excel vba clear contents of named range


1. Clearing Contents of a Specific Named Range

In this section, I’ll show you a simple solution on how to clear the contents of a particular Named Range. Suppose you have a set of data which refers to ‘X’ Named Range. You can clear it by executing a simple VBA code. Let’s have a look at the description below.

Steps:

  • First, go to the Developer Tab and then select Visual Basic.

Clearing Contents of a Specific Named Range

  • After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

Let’s make a Named Range real quick. There are other ways to do it. But this is the fastest one.

  • Select the range B5:B12 and type any name in the Name Box. In this case, the name of the Named Range is nm.

excel vba clear contents of named range method 1

  • Now, type the following code in the VBA Module.
Sub ClearNameRange()
Dim mn_NameRange As Name
For Each mn_NameRange In Names
    If LCase(Right(mn_NameRange.Name, 2)) = "nm" Then
        Range(mn_NameRange).ClearContents
    End If
Next mn_NameRange
End Sub

This code uses a For Loop to detect the Named Range nm’ and clear the contents of it.

  • After that, go back to your sheet and run the Macro named ClearNameRange as it is the name of the current Macro.

  • Next, you will see the contents of the mn Named Range are cleared.

excel vba clear contents of named range method 1

Thus you can clear the cell contents of a specific Named Range by Excel VBA.

Read More: Excel VBA: Clear Contents If Cell Contains Specific Values


2. Clearing Contents of a Named Range by Choice

You can also clear the contents of a Named Range effectively by choosing it after running the Macro. In the previous method, you had to enter the Named Range in the code manually, which is a little bit time consuming. Because in that case, you need to enter the Named Ranges that you want to clear one by one in the VBA code. Let’s go through the steps below to see how to clear a Named Range by choosing it.

Steps:

  • First, follow the steps of Method 1 and open the VBA Module.
  • Next, type the following code in the Module.
Sub ClearNamedRangeByChoice()
    Dim mn_NamedRange As Name
    Dim mn_InputMessage As String
    On Error Resume Next
    mn_InputMessage = Application.InputBox("Enter the name of the Named Ranges:", _
    "Clear Named Ranges", , , , , , 2)
    If mn_InputMessage = "False" Then Exit Sub
    Application.ScreenUpdating = False
    Set mn_NamedRange = ActiveWorkbook.Names(mn_InputMessage)
    If Not mn_NamedRange Is Nothing Then
         mn_NamedRange.RefersToRange.ClearContents
    End If
    Application.ScreenUpdating = True
End Sub

Clearing Contents of a Named Range by Choice

Code Explanation

  • First, we named the Sub Procedure for the Macro and declared some necessary variables.
  • Next, we insert a Message Box which shows the command of putting the Named Range that will be cleared afterward.
  • After that, we set the ScreenUpdating Application to False.
  • Later, we used an If Statement and ClearContents method to clear the contents in that chosen Named Range.
  • Thereafter, the ScreenUpdating Application was set to True.
  • Finally, we run the code.
  • After that, go back to your sheet and run the Macro named ClearNamedRangeByChoice as it is the current Macro.

  • Thereafter, a Message Box will pop up. Here, I created a Named Range for the range C5:C12 and its name was Gender. I inserted this Named Range in the Message Box and then clicked OK.

  • Finally, your selected Named Range becomes all cleared.

excel vba clear contents of named range method 2

Thus you can clear the cell contents of a Named Range by choosing it.

Read More: Excel VBA to Clear Contents of Range (3 Suitable Cases)


Similar Readings


3. VBA to Clear Contents of All Named Ranges

If you don’t want any content from any Named Ranges, you need to follow the steps that I described in the following section of this method. Let’s have a look at it.

Steps: 

  • First, follow the steps of Method 1 to open a VBA Module.
  • Next, type the code written below in it.
Sub ClearAllNamedRanges()
    Dim mn_RNG As Range
    Dim mn_NameRange  As Name
    Dim mn_MessageBox As Long
    On Error Resume Next
    mn_MessageBox = MsgBox("To clear all the named ranges in " & ActiveWorkbook.Name _
    & Chr(10) & "(Click OK to Clear or Cancel to Quit.)", vbOKCancel, "Clear Named Ranges")
    If mn_MessageBox = 2 Then
         MsgBox "The process has been canceled.", vbOKOnly, "Clear Named Ranges"
         Exit Sub
    End If
    For Each mn_NameRange In ActiveWorkbook.Names
        Set mn_RNG = Nothing
        Set mn_RNG = Intersect(ActiveSheet.UsedRange, mn_NameRange.RefersToRange)
        If Not mn_RNG Is Nothing Then mn_RNG.ClearContents
    Next mn_NameRange
    Application.ScreenUpdating = True
End Sub

VBA to Clear Contents of All Named Ranges

Code Explanation

  • First, we named the Sub Procedure for the Macro and declared some necessary variables.
  • Next, we insert a Message Box which shows a command to clear the contents of all the Named Ranges in the worksheet.
  • After that, we used an If Statement which executes a command based on conditions. If we click Ok in the Message Box, the command will clear the contents of all the Named Ranges of the corresponding worksheet. And if we click Cancel, it will return a Message Box showing that the clearing process has been canceled.
  • Later, we used For Loop, If Statement, and ClearContents method to clear the contents in all the Named Ranges.
  • Thereafter, the ScreenUpdating Application was set to True.
  • Finally, we run the code.
  • Now, go back to your sheet and run the Macro named ClearAllNamedRanges.

  • After that, a Message Box will pop up. Click OK to clear the Named Ranges.

  • Finally, the contents in all the Named Ranges of the corresponding worksheet are gone.

excel vba clear contents of named range method 3

Thus you can clear all the contents of all the Named Ranges in a worksheet.

Read More: How to Clear Cell Contents Based on Condition in Excel (7 Ways)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.


Conclusion

After reading this article, we can consider that you will learn some basic ideas on how to clear the contents of a Named Range by Excel VBA. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo