Another useful little snippet from Eng-tips.
There was a question regarding how to list all the range names applied to a specified cell. Forum members electricpete and MintJulep came up with a short macro to do the job, which I have adapted to suit my personal tastes, as a User Defined Function (UDF), returning a list of names and addresses as an array function. Here is the code:
Function FindNamedRanges(mycell As Range) Dim ThisName As Name, NameList() As String, i As Long, NumRows As Long NumRows = Application.Caller.Rows.Count ReDim NameList(1 To NumRows, 1 To 2) i = 1 For Each ThisName In Names If ThisName.RefersToRange.Parent Is mycell.Parent Then If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then NameList(i, 1) = ThisName.Name NameList(i, 2) = ThisName.RefersTo i = i + 1 If i > NumRows Then Exit For End If End If Next ThisName FindNamedRanges = NameList End Function
The function returns a two column array, with the range names in Column 1 and the addresses they apply to in Column 2. To display the full array:
- Enter the function.
- Select the output range, with the function cell in the top-left corner
- Press F2 (edit)
- Press ctrl-shift enter
An alternative approach, avoiding the use of macros, is to simply list all the rangenames on a spreadsheet, then sort by address. It turns out that the poster of the original question prefered this approach. I like the UDF myself, but “disputandum non est de gustibus”, as the tortoise said to the warrior.