How to unhide or delete name ranges in Excel?

Those dealing with a large number of spreadsheets with 3rd party Excel API software is likely to deal with the duplicate name range nightmare. Occasionally when 1 worksheet is copied or moved to another workbook, more than 50 of these “duplicate name range” message pops up. Something like this:

The easy workaround is just press on “Y”, and go through all the prompts. Do not press “No”, or the Escape button, because you will be forced to type a new name for the range. But to get rid of this problem once and for all, here are few simple quick steps:

1. Delete all the visible name ranges. Do this by pressing Cntrl+F3
2. Highlight all the name ranges you want to delete, and press delete.
   1. I don’t use name ranges, so I just delete all. The fastest way to do that instead of clicking 50 times is to click on the 1st range (highlight it).
   2. Then hold on to shift.
   3. Scroll down to the last name range.
   4. Continue holding onto shift; and click on the last name range.
3. Just a warning – a print area are also a “name range”. So don’t delete that if you intend to use it.
4. Click “Delete”
5. Confirm by clicking “OK”

Done. But what about the hidden name ranges? You must unhide hidden name ranges with VBA. Copy and paste the script below into VBA, to unhide all hidden name ranges in the workbook. Then repeat above steps.

Sub ShowAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub

Great. If you want to play a prank on a co-worker. All you need to do is hide those names, and if he/she didn’t know how to unhide them, a file with 100 or more name ranges can prove to be a real headache. Enjoy:

Sub HideAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = False
Next n
End Sub