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
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.