OzGrid

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

or

=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

    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.

 

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.


Gallery



stars (0 Reviews)