Formatting range addresses

What can you learn here ?
  • Easy format for debugging
  • Remove dollars for fill
  • Shorten addresses

Function to format range addresses  get it now

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 this
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 this
Private Sub sDebug(r As Range)
    Debug.Print "'" ; r.Worksheet.Name ; "!'"; r.Address

This 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 dollars
Private Sub sDebug(r As Range)
    Debug.Print "'" ; r.Worksheet.Name ; "!'"; Replace(r.Address, "$", "")

But 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$1
no worksheet name needed    $A$1
worksheet name needed       $A$1
firstcell in each area      'address'!$A$1
no row dollar 'address'!$A1
no column dollar            'address'!A$1
no dollars    'address'!A1
----------------
address2$A$1:$A$6
'address2'!$A$1:$A$6
no worksheet name needed    $A$1:$A$6
worksheet name needed       'address2'!$A$1:$A$6
firstcell in each area      'address2'!$A$1
no row dollar 'address2'!$A1:
no column dollar            'address2'!A$1:$A$6
no dollars    'address2'!A1:A6
----------------
address2$A$1:$A$6,$C$1:$E$6
'address2'!$A$1:$A$6,'address2'!$C$1:$E$6
no worksheet name needed    $A$1:$A$6,$C$1:$E$6
worksheet name needed       'address2'!$A$1:$A$6,'address2'!$C$1:$E$6
firstcell in each area      'address2'!$A$1,'address2'!$C$1
no row dollar 'address2'!$A1:,'address2'!$C1:
no column dollar            'address2'!A$1:$A$6,'address2'!C$1:$E$6
no 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


Comments