I inherited a spreadsheet that I need to clean up. It has a ton of
named ranges with spurious references. I wrote this code so I could
get a look at all the named ranges- it generates a list on a blank
tab:

Sub ShowRanges()
Dim NRange As Name

For Each NRange In Names
ActiveCell.Value = NRange.Name
ActiveCell.Offset(0, 1).Value = "'" & NRange.RefersTo
ActiveCell.Offset(1, 0).Select
Next NRange

End Sub

There are numerous cases where a named range shows up several times,
like this:
Sheet1!cccc
Sheet2!cccc
Sheet3!cccc
cccc
.... all with the same "RefersTo" range.

The named range list does not even show an entry for "cccc". The
RefersTo is
={"'Country'!$E$21:$W$45","'Country'!$E$21:$W$45 "}
but there is no tab labeled Country in the spreadsheet (I checked for
Hidden and VeryHidden tabs). When I delete "cccc" via code, the
entries specific to tabs remain in the list.

What do these entries represent?

Thanks~
Dave O

Both Workbooks and Worksheets may have child Name objects.

Sheet1!cccc is a sheet-level Name object contained by the worksheet
"Sheet1", and it is unique.

Sheet2!cccc is also a sheet-level Name object, but contained by the
worksheet "Sheet2".

cccc is a workbook-level Name object that has its own definition.


In article . com,
"DaveO" wrote:

I inherited a spreadsheet that I need to clean up. It has a ton of
named ranges with spurious references. I wrote this code so I could
get a look at all the named ranges- it generates a list on a blank
tab:

Sub ShowRanges()
Dim NRange As Name

For Each NRange In Names
ActiveCell.Value = NRange.Name
ActiveCell.Offset(0, 1).Value = "'" & NRange.RefersTo
ActiveCell.Offset(1, 0).Select
Next NRange

End Sub

There are numerous cases where a named range shows up several times,
like this:
Sheet1!cccc
Sheet2!cccc
Sheet3!cccc
cccc
... all with the same "RefersTo" range.

The named range list does not even show an entry for "cccc". The
RefersTo is
={"'Country'!$E$21:$W$45","'Country'!$E$21:$W$45 "}
but there is no tab labeled Country in the spreadsheet (I checked for
Hidden and VeryHidden tabs). When I delete "cccc" via code, the
entries specific to tabs remain in the list.

What do these entries represent?

Thanks~
Dave O

And they're all hidden, so not seen in the list. Can these named
ranges be VeryHidden?

Thanks for your help~

No. From XL/VBA Help ("Visible Property"):

True if the object is visible. For a chart or worksheet,
this property can be set to xlVeryHidden. This hides the
object so that the only way for you to make it visible
again is by setting this property to True (the user
cannot make the object visible). Read/write Boolean or Long.





In article .com,
"DaveO" wrote:

And they're all hidden, so not seen in the list. Can these named
ranges be VeryHidden?

I don't think you'll find a better utility to work with names than Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

DaveO wrote:

I inherited a spreadsheet that I need to clean up. It has a ton of
named ranges with spurious references. I wrote this code so I could
get a look at all the named ranges- it generates a list on a blank
tab:

Sub ShowRanges()
Dim NRange As Name

For Each NRange In Names
ActiveCell.Value = NRange.Name
ActiveCell.Offset(0, 1).Value = "'" & NRange.RefersTo
ActiveCell.Offset(1, 0).Select
Next NRange

End Sub

There are numerous cases where a named range shows up several times,
like this:
Sheet1!cccc
Sheet2!cccc
Sheet3!cccc
cccc
... all with the same "RefersTo" range.

The named range list does not even show an entry for "cccc". The
RefersTo is
={"'Country'!$E$21:$W$45","'Country'!$E$21:$W$45 "}
but there is no tab labeled Country in the spreadsheet (I checked for
Hidden and VeryHidden tabs). When I delete "cccc" via code, the
entries specific to tabs remain in the list.

What do these entries represent?

Thanks~
Dave O

--

Dave Peterson