How to Selectively Delete Name Ranges in Excel using a VBA macro?

In a prior post (How to unhide or delete name ranges in Excel?), we talked about how to unhide and use the name range interface (Ctrl+F3) to select and delete name ranges. But what if you want to use VBA to delete name ranges quickly or with certain conditions?

It is not uncommon to see older worksheets ending up with hundreds of old or stale name ranges. You do not have to deal with them manually. The VBA code below will help you manage name ranges quickly and efficiently:

Delete All Name Ranges

Sub DeleteAllRanges()

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Delete
Next n

End Sub

Delete All Name Ranges EXCEPT Print Area

When we set a certain part of a spreadsheet as the print area (the portion of the spreadsheet that is to be printed by default), the cell areas are saved as a name range. If we run the macro above (DeleteAllRanges), then we would lose the print area.

To prevent this, we should check the name of each name range in the loop before deleting them. Fortunately, all the “Print Area” name ranges have the name “Print_Area”, so it is quite easy to identify these special name ranges.

Basically a simple if statement can filter and prevent these name ranges from being deleted:

If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete

Complete code:

Sub DeleteAllRangesExceptPrintArea()

  Dim n As Name
  
  For Each n In ActiveWorkbook.Names
    If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete
  Next n

End Sub

Delete All Name Ranges With #REF Value

Sometimes an Excel workbook inherits a lot of stale name ranges that no longer apply to the current project. This may occur from making a new version of an older workbook, or copying worksheets from another workbook that has had a lot of work done on it. Sometimes you end up with a lot of name ranges with #REF and are completely stale and useless, meaning they should be deleted.

Note that this is not the same as a name range pointing to a cell with #REF value. This only deletes name ranges that no longer links to an actual cell because the cell or the worksheet has been deleted.

Sub DeleteAllREFRanges()
  Dim n As Name
  
  For Each n In ActiveWorkbook.Names
    If InStr(n.Value, "#REF!") > 0 Then n.Delete
  Next n

End Sub

Delete All Visible Name Ranges

This version – we delete all name ranges that is visible (not hidden). The “n.Visible” is the property that determines whether a name range is visible or not.

Sub DeleteAllVisibleRanges()
  Dim n As Name
  
  For Each n In ActiveWorkbook.Names
    If n.Visible Then n.Delete
  Next n

End Sub

Delete All Hidden Name Ranges

Very similar to the above, except we are look for name ranges that is hidden. This means .Visible property is set to false.

Sub DeleteAllHiddenRanges()
  Dim n As Name
  
  For Each n In ActiveWorkbook.Names
    If n.Visible = False Then n.Delete
  Next n

End Sub

Comments from the Reader

Do you find yourself manually choosing the right name ranges to delete? Are you trying to make different filter rules? Feel free to post in the comment section on how you delete your name ranges, and share your challenges.