Checking named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

learner - 10 Nov 2006 21:35 GMT

Hi,

I have an excel sheet which has a number of named cells (say NM1, NM2,
NM3, etc). I do not use all the names everytime. Is there a way to
check from a macro if a named range is being used in the active sheet
or not ?

Any help would be appreciated !

Reply to this Message

Gary''s Student - 10 Nov 2006 23:05 GMT

If the named range contained only empty cells, would you consider it used?

Signature

Gary's Student

> Hi,
>
[quoted text clipped - 4 lines]
>
> Any help would be appreciated !

Reply to this Message

Earl Kiosterud - 10 Nov 2006 23:10 GMT

Learner,

Paste this from here into a module. Watch for email-induced line feeds. If
there are more than 65K names, it will auger in.

Sub NamedRangeList()
Dim NamedRange As Name
Dim Roww As integer

Range("A:B").ClearContents ' blow off existing stuff
Range("A1") = "Name" ' Heading
Range("B1") = "Refers to"
Roww = 2
For Each NamedRange In Names
Cells(Roww, 1) = NamedRange.Name
Cells(Roww, 2) = "'" & NamedRange.RefersTo
Roww = Roww + 1
Next NamedRange
End Sub

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Hi,
>
[quoted text clipped - 4 lines]
>
> Any help would be appreciated !

Reply to this Message

Earl Kiosterud - 11 Nov 2006 02:28 GMT

Learner,

Oops. I forgot you wanted to know which cells have stuff in them. Here's
an update:

Sub NamedRangeList()
Dim NamedRange As Name
Dim Roww As Integer

Range("A:C").Clear ' blow off existing stuff
Range("A1") = "Name" ' Heading
Range("B1") = "Refers to"
Range("C1") = "Contents"

Roww = 2
For Each NamedRange In Names
Cells(Roww, 1) = NamedRange.Name
Cells(Roww, 2) = "'" & NamedRange.RefersTo
Cells(Roww, 3) = ActiveSheet.Range(NamedRange.Name).Value

Roww = Roww + 1
Next NamedRange
End Sub

I haven't seen it, but you may well be better off with Jan's Name Manager.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Learner,
>
[quoted text clipped - 23 lines]
>>
>> Any help would be appreciated !

Reply to this Message

Dave Peterson - 10 Nov 2006 23:23 GMT

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

There's an option to show if the name is used in cell formulas or other names.
But this won't check any code that refers to that range--or other workbooks that
refer to that name.

> Hi,
>
[quoted text clipped - 4 lines]
>
> Any help would be appreciated !

Signature

Dave Peterson

Reply to this Message