Listing or Changing all Defined Range Names (using VBA)

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" <[email protected]> wrote in
message news:[email protected]...
>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