Delete Multiple Named Ranges Quickly

Add to Favorites

This macro will allow you to delete multiple named ranges very quickly. When you run this macro, a message box will pop-up and ask you if you want to delete a named range; you then hit "Yes" or "Cancel" and then a message box will pop-up for the next named range with the same options as before. This will occur for every named range in the workbook. This way, you can see what the named range is and then quickly delete a large number of them if you need to. If you hit "Yes" on the message box, the named range will be deleted and if you hit "Cancel" the named range will not be deleted.

Where to install the macro:  Module

Delete Multiple Named Ranges Quickly

Sub Delete_Multiple_Named_Ranges_Quickly()
Dim nName As Name
Dim lReply As Long
    For Each nName In Names
        lReply = MsgBox("Delete the named range " & nName.Name _
            & vbNewLine & "It refers to: " & nName.RefersTo, _
            vbQuestion + vbYesNoCancel)         If lReply = vbCancel Then Exit Sub         If lReply = vbYes Then nName.Delete     Next nName End Sub





Similar Content on TeachExcel
Delete Empty or 'Broken' Named Ranges (#REF!)
Macro: This macro will delete named ranges which refer to empty or #REF! ranges. This means that ...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Dynamic Formulas that Update When you Add Data in Excel
Tutorial: I'll show you how to make formulas and functions that automatically update when more data...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...
Select Ranges of Cells in Excel using Macros and VBA
Tutorial: This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Delete Empty or 'Broken' Named Ranges (#REF!)
Macro: This macro will delete named ranges which refer to empty or #REF! ranges. This means that ...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Excel Forum