number of named ranges in a sheet

[rassten]

Hi
Is there a way to get the numbers of named ranges on a sheet, or even better to get a list of the names.

 

[Gary McMaster]

Select a cell in an empty area of the sheet:

Ribbon bar - Formulas
Defined Names
Use in Formula
Paste Names

Select the "Paste List" button.

Gary

 

[rassten]

Thank you Gary.

I did not know of that function before.

 

[VoG]

Maybe this will help

Sub ListNames()
Dim nm As Name, s As String
For Each nm In ActiveWorkbook.Names
    s = nm.Name & vbTab & nm.RefersTo
Next nm
MsgBox s
End Sub

 

[VoG]

Select a cell in an empty area of the sheet:

Ribbon bar - Formulas
Defined Names
Use in Formula
Paste Names

Select the "Paste List" button.

Gary

Doh!

 

[rassten]

Sorry VoG/Peter
Your macro only tells me the last named range.
So by using Gary metode im trying

Range("m1").ListNames

 

[VoG]

Double

For what it is worth

Sub ListNames()
Dim nm As Name, s As String
For Each nm In ActiveWorkbook.Names
    s = s & nm.Name & vbTab & nm.RefersTo & vbNewLine
Next nm
MsgBox s
End Sub

 

[rassten]

VoG/Peter

Just to let you know-

Thank you, the last one works just fine.

 

[VoG]

Thanks but Gary's solution is (much) better.