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

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

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

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

Darren
Kay wrote:
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

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

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


--
Gary''s Student - gsnu2007a


"Kay" wrote:

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

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

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

"Darren Hill" wrote:

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

Darren
Kay wrote:
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

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

Thanks Guys

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