Creating Hidden Range Names

[Guest]

I have a spreadsheet with some workbook wide range names that do not appear in the Insert|Name|Define dialogue box
I assume this is because these are 'hidden' range names, which I have not come across before
Can anyone advise
- how I can get a list of hidden range names in a workbook
- how are hidden range names created
Many thank
KGB

 

[A.W.J. Ales]

KGB,

The following routine will show(1st) or delete(2nd) all the hidden names in
your (active) file :
I don't know what more will create hidden names, but I do know that
pivottables is one of the causes of hidden names.


Sub ListHiddenNames()
Dim n As Name
Dim C As Integer
C = 0
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
C = C + 1
Workbooks("ListHiddenNames.xls").Worksheets(1).Cells(C, 1).Value =
n.Name
Workbooks("ListHiddenNames.xls").Worksheets(1).Cells(C, 2).Value = n
End If
Next
End Sub


Sub DeleteHiddenNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
n.Delete
End If
Next
End Sub


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

I have a spreadsheet with some workbook wide range names that do not

appear in the Insert|Name|Define dialogue box.

 

[Bob Phillips]

For Each nme In Activeworkbook.Names
Debug.Print nme.Name
Next nme

activeworkbook.names("VAT").visible=false

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a spreadsheet with some workbook wide range names that do not

appear in the Insert|Name|Define dialogue box.

 

[Jim Rech]

To deal with hidden names and to work with names in general you can make
your like a lot easier by grabbing this utility by Jan Karel Pieterse and
his gang:

http://www.bmsltd.ie/DLCount/DLCount.asp?file=NameManager.zip

--
Jim Rech
Excel MVP
|I have a spreadsheet with some workbook wide range names that do not appear
in the Insert|Name|Define dialogue box.
| I assume this is because these are 'hidden' range names, which I have not
come across before.
| Can anyone advise:
| - how I can get a list of hidden range names in a workbook.
| - how are hidden range names created.
| Many thanks
| KGB

 

[Bob Phillips]

Not directly AFAIK, but you could get hold of Jan Karel Pieterse and Charles
Williams' NameManager utility, that will help you do it in a nice utility.
You can get it at http://www.bmsltd.ie/MVP/Default.htm under Jan Karel's
section.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)