Formatting range addresses

Page Content hide
1 What can you learn here?
2 What’s wrong with .address

What can you learn here?

  • Easy format for debugging
  • Remove dollars for fill
  • Shorten addresses

This article shows some techniques to manipulate text representations of range addresses. The functions mentioned are all included in the downloadable getting started workbook.

What’s wrong with .address

Yes there is a perfectly good property of a range, range.address that returns the text address of a range. However it only returns the address, not the worksheet name. So we need to add that too.Private Sub sDebug(r As Range)    Debug.Print r.Worksheet.Name; r.Address

Which gives us

address$A$1

But that’s not formatted, and in case there are some special characters in the worksheet name, we also need to sourround it in single quotes. So actually, what we need is thisPrivate Sub sDebug(r As Range)    Debug.Print "'" ; r.Worksheet.Name ; "!'"; r.AddressThis is all fine, but we still have $a$1 addresses. This means that in the case where we are using this to fill in a formula programatically, and then to fill it down as a series, we are going to get the wrong result –  So we may want to remove the dollarsPrivate Sub sDebug(r As Range)    Debug.Print "'" ; r.Worksheet.Name ; "!'"; Replace(r.Address, "$", "")ut maybe we want to remove only the column $, or the row $ …. Let’s abandon this approach, and use a function to take care of it. Look at this test, using the function Sad() instead.

Option Explicit
Public Sub testSads()
    sDebug Sheets("address").Range("a1")
    sDebug Sheets("address2").Range("a1:a6")
    sDebug Union(Sheets("address2").Range("a1:a6"), Sheets("address2").Range("c1:e6"))
End Sub
Private Sub sDebug(r As Range)
    Debug.Print r.Worksheet.Name; r.Address
    Debug.Print SAd(r)
    Debug.Print "no worksheet name needed", SAd(r, r)
    Debug.Print "worksheet name needed", SAd(r, Sheets("address").Range("a1"))
    Debug.Print "firstcell in each area", SAd(r, , True)
    Debug.Print "no row dollar", SAd(r, , , True)
    Debug.Print "no column dollar", SAd(r, , , , True)
    Debug.Print "no dollars", SAd(r, , , True, True)
    Debug.Print "----------------"
End Sub

 

 

 

and the results

address$A$1'address'!$A$1no worksheet name needed    $A$1worksheet name needed       $A$1firstcell in each area      'address'!$A$1no row dollar 'address'!$A1no column dollar            'address'!A$1no dollars    'address'!A1----------------address2$A$1:$A$6'address2'!$A$1:$A$6no worksheet name needed    $A$1:$A$6worksheet name needed       'address2'!$A$1:$A$6firstcell in each area      'address2'!$A$1no row dollar 'address2'!$A1:no column dollar            'address2'!A$1:$A$6no dollars    'address2'!A1:A6----------------address2$A$1:$A$6,$C$1:$E$6'address2'!$A$1:$A$6,'address2'!$C$1:$E$6no worksheet name needed    $A$1:$A$6,$C$1:$E$6worksheet name needed       'address2'!$A$1:$A$6,'address2'!$C$1:$E$6firstcell in each area      'address2'!$A$1,'address2'!$C$1no row dollar 'address2'!$A1:,'address2'!$C1:no column dollar            'address2'!A$1:$A$6,'address2'!C$1:$E$6no dollars    'address2'!A1:A6,'address2'!C1:E6

Here is the code for Sad()

Function SAd(rngIn As Range, Optional target As Range = Nothing, _
            Optional singlecell As Boolean = False, _
            Optional removeRowDollar As Boolean = False, _
            Optional removeColDollar As Boolean = False) As String
    Dim strA As String
    Dim r As Range
    Dim u As Range
    ' creates an address including the worksheet name
    strA = ""
    For Each r In rngIn.Areas
        Set u = r
        If singlecell Then
            Set u = firstCell(u)
        End If
        strA = strA + SAdOneRange(u, target, singlecell, _
                        removeRowDollar, removeColDollar) & ","
    Next r
    SAd = left(strA, Len(strA) - 1)
End Function
Function SAdOneRange(rngIn As Range, Optional target As Range = Nothing, _
                        Optional singlecell As Boolean = False, _
                        Optional removeRowDollar As Boolean = False, _
                        Optional removeColDollar As Boolean = False) As String
    Dim strA As String
    ' creates an address including the worksheet name
    strA = AddressNoDollars(rngIn, removeRowDollar, removeColDollar)
    ' dont bother with worksheet name if its on the same sheet, and its been asked to do that
    If Not target Is Nothing Then
        If target.Worksheet Is rngIn.Worksheet Then
            SAdOneRange = strA
            Exit Function
        End If
    End If
    ' otherwise add the sheet name
    SAdOneRange = "'" & rngIn.Worksheet.Name & "'!" & strA
End Function
Function AddressNoDollars(a As Range, Optional doRow As Boolean = True, _
                        Optional doColumn As Boolean = True) As String
' return address minus the dollars
    Dim st As String
    Dim p1 As Long, p2 As Long
    AddressNoDollars = a.Address
    If doRow And doColumn Then
        AddressNoDollars = Replace(a.Address, "$", "")
    Else
        p1 = InStr(1, a.Address, "$")
        p2 = 0
        If p1 > 0 Then
            p2 = InStr(p1 + 1, a.Address, "$")
        End If
        ' turn $A$1 into A$1
        If doColumn And p1 > 0 Then
            AddressNoDollars = left(a.Address, p1 - 1) & Mid(a.Address, p1 + 1)
        ' turn $a$1 into $a1
        ElseIf doRow And p2 > 0 Then
            AddressNoDollars = left(a.Address, p2 - 1) & Mid(a.Address, p2 + 1, p2 - p1)
        End If
    End If
End Function
Function firstCell(inrange As Range) As Range
    Set firstCell = inrange.Cells(1, 1)
End Function

Using Sad() to format range addresses gives us the following capabilities

  • We will always get a quoted worksheet name + address
  • When using to populate the .formula property of a cell you can check whether the worksheet name is needed, by supplying a target range. This is the address of the cell that will hold the .formula. If it is on the same worksheet, there is no need to use the worksheet address – sad will not include it, thus shortening all formulas
  • It can return the address of the first cell in each area of a range
  • It can eliminate either or both of the dollars making it possible to use the fill method to populate worksheets
  • You can use this UDF as a worksheet function to find the address of a named range, for example =sad(test) will fill the cell with the address of the test named range.

Let’s look at some more Get Started Snippets