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!

 

[Peter_SSs]

@robind: ..

Did you realise that you are replying to somebody who posted over 3 years ago?

 

[mikerickson]

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!)

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.

 

[gardnertoo]

Did you realise that you are replying to somebody who posted over 3 years ago?

LOL, didn't even look at the dates! I wonder why Wattjock resurrected this thread instead of starting a new one of his/her own?

 

[helmbrecbe15]

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

 

[jbehmoaras]

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

 

[dcwildcat]

The gift that keeps on giving. Someone just pointed me to this post, and it did the trick! You're a genius!

 

[nraney]

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.

 

[mikerickson]

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.