How to Delete All Named Ranges in Excel (2 Ways)

Suppose you created a huge number of named ranges in your worksheet but now there is no use for these named ranges. To delete all named ranges in Excel you need to utilize different kinds of methods. This article provides a platform how to delete all named ranges in Excel.

What Is Named Range in Excel?

A Named range in Excel means using a name that refers to cell references, formula results as well as values. It reduces appearing large values in the formula every single time and makes it easier to use. At the same time, It reduces using cell reference (such as cell B4:B15) every time. One can easily use the assigned name and get the desired result.

In this dataset, we describe an item, apple, and its sales in three different months. We like to calculate the total sales for these three months. Previously we have done this by using the Sum function with a given cell reference. But if we give the cell reference name ‘Apples’ then we don’t need to use cell reference anymore.

To delete all named ranges in Excel that are assigned previously, we have two essential methods to do it.

1. Using Name Manager to Delete All Named Ranges in Excel

The first and the easiest method is to use the Name Manager option. When it comes to definite ‘Name Manager’, this is a place where you can create, edit and delete named ranges.

Here, We have a dataset that defines sales in three different months for several fruits items.

Steps

• At first, you need to find the ‘Formulas’ tab in the ribbon and then select the ‘Name Manager’ to open.

• After opening the ‘Name Manager’ dialog box, you have all the assigned named ranges in there. Then you need to select the entire named ranges by pressing the ‘Shift’ key and selecting all the named ranges or pressing the ‘Ctrl’ button and selecting one after another. Next, click on the ‘Delete’ option.

• A prompt box will appear whether you want to delete the selected names or not. Click on ‘OK’.

• By doing this we have an empty ‘Name Manager’ dialog box that means all the named ranges are deleted.

• Previously stated that we use named range to calculate the total sum of several fruits items. But when we delete all the named ranges function from ‘Name manager’, we will find ‘Error’ in the total sales column that denotes all the assigned ranges are deleted.

As you can see, our method successfully worked to delete all named ranges in Excel.

Read More: How to Delete Named Range in Excel

2. Applying VBA Macro to Delete All Named Ranges

Another effective way to delete all named ranges in Excel is to use VBA code.

Steps

• To open VBA, you need to press ‘Alt+F11’ on your keyboard. You can include the Developer tab in the ribbon by customizing the ribbon.
• Then click on ‘Insert’ in which you’ll get the ‘Module’ option then click on it.

• Then you’ll find a ‘Module’ window where we can add the following code:

Sub Delete_named_ranges()

Dim named_range As Name
For Each named_range In Application.ActiveWorkbook.Names
  named_range.Delete
Next

End Sub

• After that, you need to minimize the ‘Module’ window and search the ‘View’ tab in the ribbon. You’ll see a ‘Macros’ tab, click on it.

• A ‘Macro’ prompt box will open. After that, select Delete_named_ranges and then click on ‘Run’.

• When you run the code, all the named ranges will be deleted and you’ll find a similar result to the previous method.

As you can see, our method successfully worked to delete all named ranges in Excel.

Read More: Excel VBA to Delete Named Range

Similar Readings

• How to Use Named Range in Excel VBA
• How to Ignore Blank Cells in Named Range in Excel
• How to Check If Named Range Exists with Excel VBA
• How to Find a Named range in Excel
  

Download Practice Workbook

Download this practice workbook.

Conclusion

Both methods are equally effective to delete all named ranges in Excel. You can use one of the two methods and get the desired result. If you have any questions you can comment below.

Related Articles

• How to Display Named Range Contents in Excel
• How to Create Dynamic Named Range in Excel
• Create Dynamic Named Range with VBA in Excel
• How to Use Dynamic Named Range in an Excel Chart
• How to Use Named Range in Excel VLOOKUP Function
• Excel INDIRECT Function with Named Range
• Excel Reference Named Range in Another Sheet
  
• How to Set Value to a Named Range with Excel VBA
• Excel VBA to Create Named Range from Selection
  
• Excel VBA to Loop Through Named Range
• Excel VBA to Delete Named Range
• Define Names with the Create from Selection Tool in Excel
• How to Paste Range Names in Excel
  
• How to Change Scope of Named Range in Excel
• How to Navigate to a Named Range in Excel