CODEWITHSUNDEEP
excelvba
Mats Lind
Mats Lind

Reputation: 934

Find the named ranges a given cell belongs to

I'm writing a personal macro that displays important info about the ActiveCell on the statusbar, like the names (if any) of tables or pivottables it belongs to, what unformatted value it holds and such.

Now I would also like to display the names of the named ranges the cell is included in. I guess I could scan through some Names Collection for the ActiveWorkbook and test with intersect, but is there any easier way?

Upvotes: 1

Views: 1065

Answers (1)

user6432984
user6432984

Reputation:

The easiest way is to loop through the names.

Function getRangeNames(Target As Range)
    Dim n As Name
    Dim s As String
    For Each n In ThisWorkbook.Names
        On Error Resume Next
        If Not Intersect(Target, n.RefersToRange) Is Nothing Then
            s = s & n.Name & ", "
        End If
        On Error GoTo 0
    Next n

    getRangeNames = Left(s, Len(s) - 2)
End Function

Upvotes: 3

Related Questions