I want to find a defined name which refers to a selected range (see the attachment, if I select range(A1:A4), I expect to get “range6” that is its defined name.)
I have written following macro for this purpose, but as the “msg boxes” shows, it gives the address of the range the defined name refers to instead of the defined name itself and in addition to this issue, I doubt that this macro is efficient and I think there should be a better way.
Can you guide me on these issues.

Option Explicit

Sub definename()


Dim i As Integer
Dim n As Variant
For Each n In Names


If n.RefersTo = "=" & Selection.Parent.Name & "!" & Selection.Address Then


MsgBox n
MsgBox n.RefersTo
End If
Next n
End Sub

Attached Files

definedname.xlsm (14.2 KB, 4 views)