#1
|
|||
|
|||
How to find a defined name which refers to a selected range?
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 |
#2
|
|||
|
|||
Googling for 5 minutes, and
VBA Express : Excel - Return Named Range Name from Refers To Value |
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
@p45cal .Thank you very much. Your reply solved the problem.
|
Tags |
defined name |
|
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 |