Get Range Address of a Excel Named Range

< Back to Search results

 Category: [Excel]  Demo Available 

Get Range Address of a Excel Named Range


Got any Excel/VBA Questions? Free Excel Help


Excel has a handy built in feature that allows us to create a list of all named ranges and their corresponding range address. This can be seen by going to Insert>Name>Paste and clicking Paste List. The trouble with this method is the list is not dynamic and must be run again to have the range names and their range addresses updated. There are time when it is far easier to have a function that includes the range address of any named range we type. Let's assume we have a range named called MyNumbers and it references Sheet1 range A1:A10. We can use a custom function like shown below;

=RangeNameAddress(MyNumbers) OR =RangeNameAddress(MyNumbers,0)

To return $A$1:$A$10


=RangeNameAddress(MyNumbers,1) or =RangeNameAddress(MyNumbers,True)

To return Sheet1$A$1:$A$10

To be able to use this custom function in a Workbook, you must first place the code below into a standard module.

  1. Open The Workbook.
  2. Go To Tools>Macro>Visual Basic Editor (Alt+F11).
  3. Then To Insert>Module.
  4. Paste In The Code.
  5. Click The Top Right X To Return To Excel.

The Code

Function RangeNameAddress(Range_Name _

          As Range, Optional SheetName As Boolean) As String


    Dim strName As String


    If SheetName = True Then

        strName = "'" & Range_Name.Parent.Name & "'!" & _



        strName = Range_Name.Address

    End If


    RangeNameAddress = strName

End Function

Now simply enter the function into any cell as shown above.


See also:

Prevent Save As in Excel
Prevent Excel Being Saved With Another Name
Prevent Save Prompts in Excel
Excel VBA: Determine Number of Pages to be Printed
Protect/Lock Excel VBA Code
Excel: Generate Unique Random Numbers


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)