About Us

Kay[_6_]

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

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

Duke Carey

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

Gary''s Student

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

Kay[_6_]

Thanks Guys

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
converting email address names in a range of cells to real names	John	Excel Worksheet Functions	1	May 19th 10 03:44 PM
how to make range names universal in workbook	april	Excel Discussion (Misc queries)	3	June 8th 09 08:33 PM
Names referring to valid range in the active workbook	avi	Excel Programming	8	October 4th 07 02:43 PM
how to copy workbook names and worksheet names to columns in acces	gokop	Excel Programming	4	August 27th 07 11:26 AM
Copying Range Names to another open workbook	ll	Excel Programming	4	April 27th 07 09:54 PM

Menu

About Us