range names

[1inus]

is it possible to list all range names for a workbook on a worksheet?
thanks

 

[erik.van.geit]

Andrew,

good try

everything was setup to work
it worked with my code but not with F3
the only mistake could be that I was absent and used another key, as I admitted in previous reply

thank you for caring
Erik

 

[Caliche]

Is there a limit to the number of range names listed?. I have a workbook with several worksheets and more than 800 range names. With F3 key and Paste list, listed only a litlle more than 100.

Thanks a lot.

Caliche

 

[erik.van.geit]

it should work for all names:
workbooklevel
sheetlevel "=Sheet1!A1"
also names like
VAT "=6%"

I used it for 1500+names

can you display some examples of names you defined and which are not displayed in the list ?

best regards,
Erik

 

[Caliche]

Hi, Erik.

I should have read vane036 link in order to understand how F3 and Paste Works.

I have a routine (I don’t remember where I found it) that “supposedly” lists all names. Here the code :

Sub List_Ranges()
‘*
‘* List all range names
‘*

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Worksheets("Ranges").Activate
ActiveSheet.AutoFilterMode = False

Range("A2:B65536").Select
Selection.Clear

Range("A1").Value = "Range name"
Range("B1").Value = "Refers to"
Row = 2 '
‘*
For Each n In ActiveWorkbook.Names
Cells(Row, 1) = n.Name
Cells(Row, 2) = " " & n.RefersTo
Row = Row + 1
Next n
‘*
Application.Calculation = xlCalculationAutomatic
End Sub

Unfortunately, in the last run I had, it didn’t list names defined with OFFSET: I don’t know why does this happen.

Although I’ve never used it, I think the best solution around is Name Manager. See the link mentioned by Plettieri.

NOTE: Excel 12 will have a “Name Manager”. See :

http://blogs.msdn.com/excel/archive/2005/10/21/483661.aspx

HTH.

Caliche

 

[erik.van.geit]

Caliche,

about your code

this part

For Each n In ActiveWorkbook.Names 
Cells(Row, 1) = n.Name 
Cells(Row, 2) = " " & n.RefersTo 
Row = Row + 1 
Next n

is nothing more than

Cells(Row, 1).ListNames

no loop needed

the other lines are "preparation of the sheet" + layout

best regards,
Erik