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 !

If the named range contained only empty cells, would you consider it used?
--
Gary's Student


"learner" wrote:

> 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 !
>
>

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
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"learner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 !
>

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.

learner wrote:
>
> 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 !

--

Dave Peterson

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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Earl Kiosterud" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> --
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "learner" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 !
>>
>
>