I have a lot of defined range names. I would like to be able to loop through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For Each...
loop?

This will help enormously with saving time, and permitting easy documentation.

Thanks in advance

Hi Post Tenebras Lux,

Use the names Names collecyion. e.g.;

'=============
Public Sub Tester()
Dim NM As Name
Dim i As Long
Dim SH As Worksheet

Set SH = ActiveSheet

For Each NM In ThisWorkbook.Names
i = i + 1
With NM
SH.Cells(i, "A").Value = .Name
On Error Resume Next
SH.Cells(i, "B") = _
.RefersToRange.Address(0, 0, External:=True)
On Error GoTo 0
SH.Cells(i, "C").Value = .Value
End With
Next NM
End Sub
'<<=============


---
Regards,
Norman


"Post Tenebras Lux" wrote in
message ...
I have a lot of defined range names. I would like to be able to loop
through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a
specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For
Each...
loop?

This will help enormously with saving time, and permitting easy
documentation.

Thanks in advance

To list range names for a workbook, on a new worksheet in that workbook
select Insert Name Paste Paste List

Hope this helps,

Hutch

"Post Tenebras Lux" wrote:

I have a lot of defined range names. I would like to be able to loop through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For Each...
loop?

This will help enormously with saving time, and permitting easy documentation.

Thanks in advance

Option Explicit
Dim rngname As Name
Dim i As Integer
Sub FindRangeName()
i = 1
For Each rngname In ActiveWorkbook.Names
Cells(i, 1).Value = rngname.Name
Cells(i, 2).Value = "'" & rngname.RefersTo
i = i + 1
Next rngname
End Sub

This code will list all of the Names Ranges in column A of sheet1 and all of
the corresponding ranges in column B.

Mike

"Post Tenebras Lux" wrote:

I have a lot of defined range names. I would like to be able to loop through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For Each...
loop?

This will help enormously with saving time, and permitting easy documentation.

Thanks in advance

Thanks to all. Just what the doctor ordered.

Have a good w/e .

"Post Tenebras Lux" wrote:

I have a lot of defined range names. I would like to be able to loop through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For Each...
loop?

This will help enormously with saving time, and permitting easy documentation.

Thanks in advance

You may want to get Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much, much easier.

Post Tenebras Lux wrote:

I have a lot of defined range names. I would like to be able to loop through
them and either list them on a worksheet, with their range address in the
adjacent cell; or change the range name to something else based on a specific
criteria.

I can handle all of the above except identifying the "collection" of range
names. Is it like worksheets collection with which I can use a For Each...
loop?

This will help enormously with saving time, and permitting easy documentation.

Thanks in advance

--

Dave Peterson