Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA to Delete Named Range (5 Suitable Examples)

To mention the results of the formula or cell references, we use the named range. It makes our formula easy and dynamic and makes it easy to understand the cell references for us. Also, we can use the named range to navigate between the Excel sheets swiftly. But, sometimes we need to delete them for our own purpose. In this article, we’ll demonstrate 5 easy and relatable examples to delete a named range using VBA in Excel. So, go through the entire article to understand it properly.
The GIF attached below is an overview image of the article and depicts its main idea.

excel vba delete named range


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


5 Examples to Delete Named Range Using VBA in Excel

For ease of understanding, we are going to use an Inventory List of ABC Technology. This dataset includes the Product ID, corresponding Product Names, and Units Remaining in columns B, C, and D respectively.

Dataset to delete named range using VBA in Excel

If we click on the drop-down arrow in the Name Box, we can see two named ranges available here. One is ID and the other one is Units.

Two named ranges in the Name Box

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll utilize this dataset to delete these named ranges using VBA in Excel. So, let’s explore them one by one.
Not to mention, here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


1. Deleting All Named Ranges from Workbook

In our first example, we’ll get to know how we can delete all named ranges from a workbook with the help of VBA code. If we select cells in the B4:B14 range, we can see that it shows ID in the Name Box. Actually, it’s the named range of this selection.

ID named range in Excel

Similarly, it’ll show Units for the D4:D14 range.

Units named range in Excel

Now, we’ll erase all those names from Excel. Just follow us along.

📌 Steps:

  • At the very beginning, navigate to the Developer tab.
  • Then, click on Visual Basic on the Code group of commands.

visual basic on the developer tab

Instantly, the Microsoft Visual Basic for Applications window appears before us.

  • At this moment, advance to the Insert tab.
  • After that, select Module from the available options.

inserting module in VBA editor in excel

It’ll insert a code module on the right side of the display.

  • Now, paste the following scripts into the module.
Option Explicit
Sub delete_all()
Dim wb As Workbook
Dim named_range As Name
Set wb = ActiveWorkbook
For Each named_range In wb.Names
named_range.Delete
Next named_range
End Sub

Code Breakdown

  • Firstly, we created a new sub-procedure.
  • Secondly, we declared the variables.
  • Thirdly, we selected each named range in the active workbook and deleted using the Delete method.
  • Lastly, the sub-procedure is being ended.

VBA code to delete named range in Excel

  • Then, click on the Run button or press F5 on the keyboard to execute the code.

Executing code

  • Therefore, return to the worksheet and select cells in the B4:B14 range again.

At this time, you won’t see the ID name because it’s already get deleted. The same thing happened for Units named range also.

named range deleted using VBA in Excel


2. Removing Named Range from Particular Worksheet

In the previous, we learned to delete a named range from the entire workbook. But what should we do to delete the named range from a specific worksheet? Don’t worry. The answer lies in the following steps. So, follow them sequentially.

📌 Steps:

If we expand the Name Box, we can see two named ranges in this sheet. ID and Units. And these ranges are highlighted in the following image also.

Two named range in the sheet

  • Now, bring another code module like Example 1 and paste the following code there.
Sub delete_worksheet()
Dim named_range As Name
For Each named_range In ActiveWorkbook.Names
If named_range.RefersToRange.Parent.Name = "Sheet3" Then named_range.Delete
Next named_range
End Sub

VBA code to delete named range from specific sheet in excel

  • Then, Run the code like before.
  • Afterward, select the range of ID and see, it isn’t showing the name anymore.

Removing Named Range from Particular Worksheet in Excel


3. Deleting Named Range with Certain Text

As we already know, we have two named ranges in our dataset. One is ID, another is Units.

two named range in excel name box

Now, we want to delete the name which contains “Un” in its name. As we have only two named ranges, we can understand easily that the Units named range is going to be deleted. Let’s see it in action.

📌 Steps:

  • Similarly, get another code module and write down the following code into it.
Sub delete_certain_text()
Dim named_range As Name
For Each named_range In Application.ActiveWorkbook.Names
If InStr(1, named_range.Name, "Un", vbTextCompare) > 0 Then named_range.Delete
Next
End Sub

VBA code to Delete Named Range with Certain Text

  • After executing the code and returning to the worksheet, we can see that there is only one name ID available. And the Units one is already deleted.

Deleted Named Range with Certain Text in Excel


4. Eliminating Hidden Named Range

Sometimes, there happens to be hidden named ranges present in the Excel file. We can’t find them in the Name Manager also. See the image below.

hidden named range in name box

Look, there are no names visible. But we can clearly notice that there is some void space. Actually, there are some hidden named ranges available that we cannot select or cannot operate on them. Now, we’ll clear those hidden named ranges using VBA in Excel. So, let’s be with us.

📌 Steps:

  • Firstly, open a code module like before and paste the following code there.
Option Explicit
Sub delete_hidden()
Dim Rng As Name
For Each Rng In ActiveWorkbook.Names
If Rng.Visible = False Then Rng.Delete
Next Rng
End Sub

VBA code to Eliminate Hidden Named Range

Right after executing the code, the named ranges will be deleted forever. We cannot show this after-effect, as the named range was hidden before.


5. Eradicating Named Range Excluding Print Area

The sheet sections are recorded as a named range when we choose a specific portion of that sheet as the print area. We would lose the print area if we ran the macros which we have shown in the previous examples. As a result, we should first verify the names of each named range in the cycle before eliminating them to avoid problems. Thankfully, the name of every named range from within the portion is “Print Area,” making it simple to recognize these unique name ranges. Just follow these simple steps.

📌 Steps:

  • At first, bring the code module like before and paste the following script into it.
Sub delete_except_print()
Dim named_rng As Name
  For Each named_rng In ActiveSheet.Names
    If Right(named_rng.Name, 11) <> "!Print_Area" And named_rng.Name <> "Print_Area" Then named_rng.Delete
  Next named_rng
End Sub

VBA code to Eradicate Named Range Excluding Print Area

  • Accordingly, execute the code and see the result below.

Eradicating Named Range Excluding Print Area in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice section


Conclusion

This article explains how to delete a named range using VBA in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo