Finding all range names in a specified range

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.

This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

2 Responses to Finding all range names in a specified range

  1. Jace Garfield says:

    Great website. Alot of good information. I will be looking at this often to learn more.
    I am wondering if you can help me with a challenge that I am having.

    Using “=countifs “returns the number of times that logical conditions are met for a set of criteria within a range. Once this is done is there a formula or a means by which you can find the cell adress of this occurence? I need to be able to find the address to then find a coresponding value in another cell 2 collumns over.


  2. dougaj4 says:

    Hi Jace, thanks for the comments.

    There are two parts to your question:
    – matching rows after the first match
    – matching with multiple criteria

    The first part can be done with a combination of Match and Offset:
    This formula is in cell C7, the value to be matched is in B7, the data range starts in A20, C6 is empty or contains zero, and D3 contains the number of rows in the data range. If you copy that down (with the match value in column B copied down as well), the Offset formula adjusts the range, and the formula should return the row for each matching cell.

    I posted a UDF to do the second part some time ago (

    I’m going to modify that function to also return an array with each matching row number, rather than just the first match.

    Should appear in the next day or two.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s