How Can I Loop through all the range names in a workbook in VBA?

[Kay]

Hi, I am using Excel2002 SP3
I need to identify all the range names within a workbook

Any ideas? My below code does not work error message 'argument not
optional'

Dim ws As Worksheet
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets
For Each rng In ws.Range
Select Case rng.Name
Case "rangename"
do soemthing
Case Else
End Select
Next
Next

 

[Darren Hill]

Try this:
Sub IterateNames()
Dim ws As Worksheet
Dim nm As Name
For Each ws In ActiveWorkbook.Worksheets
For Each nm In ws.Names
Select Case nm.Name

Case "rangename"
'do soemthing
Case Else
Debug.Print nm.Name
End Select
Next
Next
End Sub

 

[Guest]

Sub name_it()
Dim n As Name
Dim r As Range
Dim s As String
For Each n In ThisWorkbook.Names
MsgBox (n.Name)
s = Range(n).Address
MsgBox (s)
Next n
End Sub

 

[Guest]

Have you tried Insert->Names->paste list? BE SURE TO SELECT A LARGE RANGE
OF EMPTY CELLS FIRST

 

[Kay]

Thanks Guys

Both worked - should have been looking at Name rather than Range