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

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

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