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

"avi" wrote in message
ups.com...
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