Checking named range

L

learner

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

Earl Kiosterud

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
 
D

Dave Peterson

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.
 
E

Earl Kiosterud

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.
 
Top