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

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
>

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
>
>

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
> >
>

Thanks Guys

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