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:

A formula or sheet you want to move or copy contains the name ..., which already exists on the destination worksheet. Do you want to use this version of the name?

 

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.Name range manager - Are you sure you want to delete the selected items?
  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

3 thoughts on “How to unhide or delete name ranges in Excel?

  1. ed says:

    What does the “row=1” statement do? I just deleted it.

    The unhide worked well. I had some ghost file links that I couldn’t fix, but this allowed me to see them and remove them. Excel 2010.

  2. Tina says:

    THANK YOU!!!!
    Whoever decided to HIDE names in my workbook… well… let’s just say, it’s a good thing I don’t know who they are.
    But this quick VBA code to unhide them all is a lifesaver. 60 hidden name/rename errors every time I copied a chart… ugh! now gone!!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>