Getting the address of a selected range

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
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:

Click link above to download

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

4 Responses to Getting the address of a selected range

  1. Rob van Gelder says:

    Could you not use this?

    Function RngAddress(Rng As Range, Optional NumRows As Long, Optional NumCols As Long, Optional SheetName As Boolean) As String
    RngAddress = Rng.Resize(IIf(NumRows = 0, Rng.Rows.Count, NumRows), IIf(NumCols = 0, Rng.Columns.Count, NumCols)).Address(External:=SheetName)
    End Function

  2. dougaj4 says:

    Rob – yes that works, I’ll add your version to spreadsheet if that’s OK.

  3. Rob van Gelder says:

    No problem. Help yourself.

  4. Pingback: Daily Download 30: Data Transfer, to and from VBA | Newton Excel Bach, not (just) an Excel Blog

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