Meraz Al Nahian
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.
Excel VBA to Clear Contents of Named Range: 3 Macro Variants
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.
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.
- First, go to the Developer Tab and then select Visual Basic.
- 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.
- 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 Each 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.
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
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.
Thus you can clear the cell contents of a Named Range by choosing it.
Read More: Excel VBA to Clear Contents of Range
Similar Readings
- How to Clear Cells with Certain Value in Excel
- Difference Between Delete and Clear Contents in Excel
- How to Clear Cells in Excel VBA
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
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.
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
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
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.
Related Articles
- How to Clear Multiple Cells in Excel
- How to Clear Contents in Excel Without Deleting Formatting
- How to Clear Contents Without Deleting Formulas Using VBA in Excel
- How to Clear Formatting in Excel
- How to Remove Formatting in Excel Without Removing Contents
- How to Clear Recent Documents in Excel
- How to Clear Excel Temp Files
- How to Clear Contents of a Sheet with Excel VBA
