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.

Open The Workbook.
Go To Tools>Macro>Visual Basic Editor (Alt+F11).
Then To Insert>Module.
Paste In The Code.
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

    Application.Volatile



    If SheetName = True Then

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

        Range_Name.Address

    Else

        strName = Range_Name.Address

    End If

   

    RangeNameAddress = strName

End Function

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