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:
- Delete all the visible name ranges. Do this by pressing Cntrl+F3
- Highlight all the name ranges you want to delete, and press delete.
- Just a warning – a print area are also a “name range”. So don’t delete that if you intend to use it.
- Click “Delete”
- 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?”
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.
ed – yes you are correct, the statement is not needed and I have taken it out.
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!!