The Excel Indirect() function allows other functions to use the text in a worksheet cell to define a range, rather than selecting the range, or entering it directly into the function. For instance, if cell A1 contained the text B1:B10, then the function =Sum(Indirect(A1)) would return the sum of the values in B1:B10.

It would be convenient if there was a built in Excel function that returned the address of a selected range, but there isn’t. It is possible to create the address using a combination of functions and text strings, but in my opinion a much simpler method is available through a very short piece of VBA:

Function RngAddress(Rng As Range) As String
RngAddress = Rng.Address
End Function

Pasting those three lines into a code module in the VB Editor will create a RngAddress function that will return a text string with the address of a selected range.

But having done that, wouldn’t it be nice if we could also just select the top left hand corner, and enter the number of rows and columns we wanted. Also being able to (optionally) return the worksheet name would be useful. Another few lines will do what we want:

Function RngAddress(Rng As Range, Optional NumRows As Long, Optional NumCols As Long, Optional SheetName As Boolean) As String

' If you want this function to update with every worksheet change, then un-comment the line below
' Application.Volatile

If NumRows = 0 And NumCols = 0 Then
RngAddress = Rng.Address
    Else
If NumRows = 0 Then NumRows = Rng.Rows.Count
If NumCols = 0 Then NumCols = Rng.Columns.Count
RngAddress = Rng.Cells(1, 1).Address & ":" & Rng.Cells(NumRows, NumCols).Address
    End If

If SheetName = True Then RngAddress = "'" & Rng.Worksheet.Name & "'" & "!" & RngAddress

End Function

The entire code for this function, together with some examples can be downloaded from: RngAddress.xlsb

And this is what it looks like: