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:
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)
Rob – yes that works, I’ll add your version to spreadsheet if that’s OK.
No problem. Help yourself.
Pingback: Daily Download 30: Data Transfer, to and from VBA | Newton Excel Bach, not (just) an Excel Blog