Range Defined names loop

[mp]

Hi all,
How to loop through range names?
range names like menu Insert| name | define

similar to intention of (invalid) pseudocode below
For each range in Worksheet.Ranges
Debug.Print range.name
next

since i don't find a ranges collection the above won't work
the reason i ask is i had named some ranges inconsistently and wanted to run
a quick macro to fix them
eg
labor00
labor2001
....
should be
labor2000
labor2001
...
thanks
mark

 

[Rick Rothstein]

Forget using a macro.... download Jan Karel Pieterse's NameManager Add-In...
it will allow you to do lots of things with Defined Names. After you install
it, it will be available in the Tools item on the Menu Bar. To rename a
Defined Name, just double click it in the NameManager list.

http://www.oaltd.co.uk/DLCount/DLCount.asp?file=NameManager.zip

 

[Rick Rothstein]

However, if you wish to do this the "macro" way, this is how you would
iterate through the Names collection...

Dim N As Name
For Each N In Application.Names
Debug.Print N.Name & " ==> " & N.RefersTo
Next

Just address the appropriate properties of each iterated name as needed.

 

[Patrick Molloy]

its definitely not a good idea to use keywords for variables. yuo use range
as a Range object...

dim cell as range
then use the variable called cell

to check a "name" you could use
debug.print Range("A1").Name.Name

this will raise an error if there is no name, so

for each cell in Selection
on error resume next
debug.print cell.Address(False,False), Cell.Name.Name

 

[mp]

its definitely not a good idea to use keywords for variables. yuo use
range
as a Range object...

absolutely, that was just pseudocode to show the idea

dim cell as range
then use the variable called cell

to check a "name" you could use
debug.print Range("A1").Name.Name

this will raise an error if there is no name, so

for each cell in Selection
on error resume next
debug.print cell.Address(False,False), Cell.Name.Name
on error goto 0
next

thanks i'll give it a try
mark

 

[mp]

Thanks will also check the addin

However, if you wish to do this the "macro" way, this is how you would
iterate through the Names collection...

Dim N As Name
For Each N In Application.Names
Debug.Print N.Name & " ==> " & N.RefersTo
Next

Just address the appropriate properties of each iterated name as needed.