Hidden Named Ranges in Excel

P

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
Hello all,

Does anyone know how to find hidden named ranges in Excel?

My company has added software to Excel where the software can determine errors in your spreadsheet. For example, this software can find cells that reference blank cells and cells that have the same color font and background (as in, you are trying to hide the results of the formula). In addition, this software lists out all named ranges in a spreadsheet.

When I ran the software, it was determined that I have many named ranges within this spreadsheet. My first step was to delete all named ranges by choosing Insert-Name-Define and deleting all named ranges (including set print areas). Basically, I was trying to get rid of all named ranges.

After I did this, there are still many named ranges within Excel, even though they do not show up in the Insert-Name-Define menu. I tried to do a find (with Control-F) for some of these ranges, but was unsuccessful. Does anyone know how to delete these hidden named ranges?

One related issue - Every time I try to copy a sheet in this file, I get numerous messages indicating "A formula or sheet you want to move or copy contains the name "XXXXXXX", which already exists on the destination worksheet. Do you want to use this version of the name?" I get this message with every named range that exists (11 times). The "XXXXXXX" can be replaced with the other named ranges.

One final note - I am using Excel 2002.

Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi
You may find that some of the names are sheet specific - i.e just like Print_Area they are only viewed when that sheet is active. Try activating each sheet and checking names. Alternatively are there any hidden sheets with named ranges?

Wolfshead
 
Upvote 0
To unhide any hidden names:

Code: 
Sub Test()
Dim nName as Name
For Each nName in ActiveWorkbook.Names
    nName.Hidden = False
Next nName
End Sub
 
Upvote 0
here's a macro that will delete all named ranges:

Code: 
Sub DeleteNames()
Dim nmeName As Name
    On Error Resume Next
    For Each nmeName In ActiveWorkbook.Names
'        MsgBox nmeName.Name
        nmeName.Delete
    Next nmeName
    On Error GoTo 0
End Sub
 
Upvote 0
here's a macro that will delete all named ranges:
Click to expand...

Hi btadams

Note:

Your code will not only delete named ranges but, in fact, all names, also the named constants or named formulas.
 
Upvote 0
Named ranges play a very important role in developing good spreadsheet models. Deleting them is the wrong way to go. See
Names and formatted formulas
http://www.tushar-mehta.com/publish_train/data_analysis/08.htm

pagrender said:
Hello all,

Does anyone know how to find hidden named ranges in Excel?

My company has added software to Excel where the software can determine errors in your spreadsheet. For example, this software can find cells that reference blank cells and cells that have the same color font and background (as in, you are trying to hide the results of the formula). In addition, this software lists out all named ranges in a spreadsheet.

When I ran the software, it was determined that I have many named ranges within this spreadsheet. My first step was to delete all named ranges by choosing Insert-Name-Define and deleting all named ranges (including set print areas). Basically, I was trying to get rid of all named ranges.

After I did this, there are still many named ranges within Excel, even though they do not show up in the Insert-Name-Define menu. I tried to do a find (with Control-F) for some of these ranges, but was unsuccessful. Does anyone know how to delete these hidden named ranges?

One related issue - Every time I try to copy a sheet in this file, I get numerous messages indicating "A formula or sheet you want to move or copy contains the name "XXXXXXX", which already exists on the destination worksheet. Do you want to use this version of the name?" I get this message with every named range that exists (11 times). The "XXXXXXX" can be replaced with the other named ranges.

One final note - I am using Excel 2002.

Thanks!
Click to expand...
 
Upvote 0
I have a similar problem, in that after a number of years of development, our template has amassed a number of named ranges (30+) that refer to nothing (reference errors, spreadsheets that don't exist any more. I do not want to mess up any print set up, is there a quick way to delete all named ranges except those that have to do with print setup? Do named ranges to external sources take up a lot of space? Our file has recently increased by 1MB for no apparent reason.

Thanks
 
Upvote 0
going thru the Insert,Name,Define menu option I find in a Named Range a Name that has what looks like an ASCI II character after it that looks like a square or rectangle like this . It links to an html site. I have tried all manner of macros found on this site yet the name remains... I've even tried the Delete Link Add-in from Microsoft and still the name remains.

Can anyone help?

Thanks in advanced!
 
Upvote 0
@robind: Instead of deleting the bad names, maybe they just need to be fixed? Here's code to give you a list of names whose formula results in an error. This does not pick up names that point to empty cells, those names are valid (although maybe useless). This will find names that pointed to cells now deleted, or formula-defined ranges where the formula kicks out an error instead of an actual range of cells.

Code: 
Sub ListBadNames()
    
mycol = 1
myrow = Cells(Rows.Count, mycol).End(xlUp).Row + 3
Cells(myrow - 1, mycol).Value = "Bad Names"
For Each nm In ActiveWorkbook.Names
If Right(nm.RefersTo, 4) = "REF!" Then
    Cells(myrow, mycol).Value = nm.Name
    myrow = myrow + 1
End If
Next nm

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
389
KRice
K
A
  • Question
This formula is missing a range reference or a defined name.
Replies
1
Views
95
awoohaw
awoohaw
N
  • Question
Auto Sort for Leaderboard
Replies
0
Views
57
NTLKnight
N
S
  • Question
VBA Challenge
Replies
2
Views
214
SnowyBry
S
S
  • Question
Save current sheet as a new XLS and remove all Named Ranges
Replies
2
Views
320
sm789
S

Forum statistics

Threads
1,214,429
Messages
6,119,433
Members
448,897
Latest member
ksjohnson1970

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top