How to find a defined name which refers to a selected range?

soroush.kalantari · #1 12-11-2023, 03:16 AM

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

ArviLaanemets · #2 12-11-2023, 05:35 AM

Googling for 5 minutes, and
VBA Express : Excel - Return Named Range Name from Refers To Value

p45cal · #3 12-11-2023, 08:20 AM

The following macro will list all names (because there can be more than one) that are the same position and size as the selected range (I haven't tested it on non-contiguous ranges).
If a name is sheet-scoped (as yours is) it appears with the sheet name as prefix.

Code:

Sub isdefinename()
Dim msg As String, n, zz
For Each n In Names
  Set zz = Nothing
  On Error Resume Next
  Set zz = n.RefersToRange
  On Error GoTo 0
  If Not zz Is Nothing Then
    If zz.Address(external:=True) = Selection.Address(external:=True) Then
      If msg = "" Then msg = n.Name Else msg = msg & vbLf & n.Name
    End If
  End If
Next n
If msg = "" Then msg = "Selection is not a Name"
MsgBox msg
End Sub

soroush.kalantari · #4 12-11-2023, 08:50 PM

@p45cal .Thank you very much. Your reply solved the problem.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Range - Start at beginning of doc - End at defined stylename	TheBigBoss	Word VBA	2	10-18-2021 07:27 AM
Find if Date range falls within another range	Triadragon	Excel	3	05-02-2016 11:48 AM
Dymanic Range Starting at a Cell Defined by a Count	jap7675	Excel Programming	7	12-02-2015 02:12 AM
Data validation list filter with range defined by OFFSET	Mango123	Excel	4	03-18-2014 02:52 PM
Range.Information(wdStartOfRangeRowNumber): Application-defined or Object-defined err	tinfanide	Excel Programming	2	06-09-2012 10:19 AM