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!
 
Sort by date Sort by votes

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
This should make all names visible in the box.
It will list them onto the worksheet and highlight the ones that evaluate to an error (like #REF!)
Code: 
Sub test()
    Dim oneName As Name
    For Each oneName In ThisWorkbook.Names
        oneName.Visible = True
    Next oneName
    
    With Range("a1")
        .Resize(1, 3).EntireColumn.ClearContents
        .ListNames
        
        With Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
            .Offset(0, 1).Formula = .Value
            On Error Resume Next
            .Offset(0, 1).SpecialCells(xlCellTypeFormulas, xlErrors).Select
            On Error GoTo 0
        End With
        
    End With
End Sub
Names that are scoped to sheets other than the ActiveSheet aren't included in this.
 
Upvote 0
Mike, thanks for posting. I have a string going and your script solves all my problems. I had some extra issues that you got me around. If you care to know what else, please check that out. I'm posting an all clear on that and directing to this string. Thank you! :)

Brett
 
Upvote 0
These two lines of code have saved my life:
For Each oneName In ThisWorkbook.Names
oneName.Visible = True
Next oneName

I have been looking for a solution to getting rid of hidden named ranges forever (since 2008 at least) and was never able to find a solution ... and it was so simple :LOL:
 
Upvote 0
The gift that keeps on giving. Someone just pointed me to this post, and it did the trick! You're a genius!
 
Upvote 0
Mike, thank you very much for this. MS needs to build this into an incorporated feature and give you lots of money for it. Or, better yet, solve the issue that creates the hidden names in the first place.
 
Upvote 0
It sounds like the OP's issue is that a third party wanted the names in thier routine to be not .Visible. As I do in some of my code. As far as I can see, there is no issue to be fixed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Error: Excel found a problem with one or more formula references in this worksheet
2
Replies
13
Views
177
Davidns
D
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
564
KRice
K
K
  • Question
Variable losing set Range
Replies
8
Views
135
MARK858
MARK858
FrumpyJones
  • Solved
Table not showing up in data validation source
Replies
9
Views
90
Georgiboy
Georgiboy
A
  • Question
This formula is missing a range reference or a defined name.
Replies
1
Views
182
awoohaw
awoohaw

Forum statistics

Threads
1,216,063
Messages
6,128,559
Members
449,458
Latest member
gillmit

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