Phantom named ranges in a workbook?
 

As an exercise I ran this code on a spreadsheet I've been using for
several years, which has been updated through several Excel releases.
It shows 76 named ranges, mostly starting with "HTML_" ; I have used
this file to manipulate data copied and pasted from web pages. The
..RefersTo shows a mix of values assigned and cell references. When I
click ~Insert ~Name ~Define, however, only one named range appears in
the list.

How does this spreadsheet have named ranges that do not appear in the
Name Define list?

Thanks- the code follows:

Sub Named_Range_Show()
Dim NamedRange As Variant
For Each NamedRange In ActiveWorkbook.Names
MsgBox NamedRange.Name & ": " & NamedRange.RefersTo
Next NamedRange
End Sub

Phantom named ranges in a workbook?
 

Names can be hidden.
Activeworkbook.Names("XYZ").Visible = False
would hide the name.
Run this code to unhide them:
Sub UnhideNames()
For each Nme in Activeworkbook.Names
Nme.Visible = True
Next
End Sub

Bob Umlas
Excel MVP

"Dave O" wrote in message
oups.com...
As an exercise I ran this code on a spreadsheet I've been using for
several years, which has been updated through several Excel releases.
It shows 76 named ranges, mostly starting with "HTML_" ; I have used
this file to manipulate data copied and pasted from web pages. The
.RefersTo shows a mix of values assigned and cell references. When I
click ~Insert ~Name ~Define, however, only one named range appears in
the list.

How does this spreadsheet have named ranges that do not appear in the
Name Define list?

Thanks- the code follows:

Sub Named_Range_Show()
Dim NamedRange As Variant
For Each NamedRange In ActiveWorkbook.Names
MsgBox NamedRange.Name & ": " & NamedRange.RefersTo
Next NamedRange
End Sub

Phantom named ranges in a workbook?
 

Yikes- wasn't aware of that. Thanks, Bob~~

Phantom named ranges in a workbook?
 

Get 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

It'll make working with names much easier.

Dave O wrote:

As an exercise I ran this code on a spreadsheet I've been using for
several years, which has been updated through several Excel releases.
It shows 76 named ranges, mostly starting with "HTML_" ; I have used
this file to manipulate data copied and pasted from web pages. The
.RefersTo shows a mix of values assigned and cell references. When I
click ~Insert ~Name ~Define, however, only one named range appears in
the list.

How does this spreadsheet have named ranges that do not appear in the
Name Define list?

Thanks- the code follows:

Sub Named_Range_Show()
Dim NamedRange As Variant
For Each NamedRange In ActiveWorkbook.Names
MsgBox NamedRange.Name & ": " & NamedRange.RefersTo
Next NamedRange
End Sub

--

Dave Peterson