Managing and removing named ranges in Excel can be a challenging task as you would have to do this one by one for each field. We're going to show you how to delete named range Excel using VBA with one click.

How to delete named range Excel

Each named range is an object in the Names collection of a workbook. We can access these names using a For Each…Next loop and checking their references with the RefersToRange method. The RefersToRange method returns the reference as a Range object that we can use to find its parent worksheet.

The worksheet name a named range belongs to can be found with Name property under the Parent method. Then, using an If statement you can find the parent worksheet name.

    For Each nm In ActiveWorkbook.Names

        If nm.RefersToRange.Parent.Name = "Sheet1" Then nm.Delete

    Next nm

You can use codes in two ways:

• Module
• Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

Delete named ranges in a worksheet

Module method:

Sub DeleteNamedRangesInWorksheet()

    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        If nm.RefersToRange.Parent.Name = "Sheet1" Then nm.Delete

    Next nm

End Sub

Immediate Window method:

For Each nm In ActiveWorkbook.Names: If nm.RefersToRange.Parent.Name = "Sheet1" Then nm.Delete: Next nm