How to Delete All Named Ranges in Excel

This post will guide you how to remove all named ranges from your workbook in Excel. How do I quickly delete all named ranges with VBA in Excel 2013/2016.

Assuming that you have created lots of named ranges in your workbook, and you wish to delete all of them at once, how to do it. You can use an Excel VBA macro to accomplish it. And this post will show you two methods to remove all named ranges.

Method1: Delete All Named Ranges


You can use Name Manager feature to delete all named ranges in your workbook, just see the following steps:

Step1: go to the Formulas tab, click Name Manager command under the Defined Names group. And the Name Manager dialog will open.

delete all named ranges18

Step2: select the first name range in the Name Manager dialog box, and press Shift key to select the all the named ranges.

delete all named ranges2

Step3: click Delete button at the top of the Name Manager dialog box, then a warning box will appear to ask you if you want to delete the selected names. Click Ok button. And all named ranges should be deleted at once.

Method2: Delete All Named Ranges with VBA


You can also use an Excel VBA Macro to remove all named ranges in a workbook. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

delete all named ranges4

Option Explicit
 
Sub RemNamedRanges()
     
    Dim nm              As Name
     
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
    On Error GoTo 0
     
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

delete all named ranges5

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar