Names referring to valid range in the active workbook

[avi]

hello,

I'm looking for a procedure that identifies Names referring to valid
range in the active workbook (and excludes all names referring to
external workbooks or to formula or to constants....)

Please help
Thanks
Avi

 

[Guest]

Hi avi.
Try:

Public Sub Macro1()
Selection.ListNames
End Sub

Regards
Eliano

 

[avi]

The procedure returns all the names while i'm interested in only valid
ranges in the active workbook

Thanks
Avi

 

[Guest]

avi,

Try this

With ActiveWorkbook
Selection.ListNames
End With

 

[Peter T]

Sub test2()
Dim cnt As Long
Dim wb As Workbook
Dim nm As Name
Dim rng As Range
Dim arrNames()

Set wb = ActiveWorkbook
cnt = wb.Names.Count

If cnt = 0 Then Exit Sub

ReDim arrNames(1 To cnt, 1 To 2)

cnt = 0

On Error Resume Next
For Each nm In wb.Names
Set rng = Range(nm.Name)
If Not rng Is Nothing Then
If rng.Parent.Parent Is wb Then
cnt = cnt + 1
arrNames(cnt, 1) = nm.Name
arrNames(cnt, 2) = "' " & nm.RefersTo
End If
Set rng = Nothing
Else
Err.Clear
End If
Next

If cnt Then
Range("A1").Resize(cnt, 2).Value = arrNames
End If

End Sub

This will also include formula type names that indirectly refer to a range
in the workbook. It will exclude range names that refer to a range in
another workbook, and other formula or non range names.

Regards,
Peter T

 

[Luca Brasi]

Peter T's code seems to do what you need, but your workbook might
contain named ranges for single worksheets.
So you should add something like

for each ws in wb.worksheets
for each nm in ws.names
....
next nm
next ws

to your code.

 

[Peter T]

Worksheet level names also exist in the workbook's Names collection, where
they are listed like this -

SheetName!LocalName

So for the purposes of this exercise it shouldn't be necessary to loop
worksheets and do 'for each nm in ws.names', unless of course the OP
requires that additional local/global info about the name.

Regards,
Peter T

 

[Luca Brasi]

your right! damn, i coded too much lines for years....

 

[avi]

Great!

Thaks a lot for your detailed help

Avi