Hidden Named Ranges in Excel

[pagrender]

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!

 

[Wolfshead]

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

 

[Andrew Poulsom]

To unhide any hidden names:

Sub Test()
Dim nName as Name
For Each nName in ActiveWorkbook.Names
    nName.Hidden = False
Next nName
End Sub

 

[btadams]

here's a macro that will delete all named ranges:

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

 

[pgc01]

here's a macro that will delete all named ranges:

Hi btadams

Note:

Your code will not only delete named ranges but, in fact, all names, also the named constants or named formulas.

 

[tusharm]

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

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!

 

[robind21283]

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

 

[Wattjock]

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!

 

[Wattjock]

I just download 'Name Manager' and tried that... still no luck. It won't delete this name.

 

[gardnertoo]

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

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