505.369.1650 [email protected]


This VBA function returns the first row, last row, first column, or last column of a Microsoft Excel range address.  It is also a good example of using a custom enumerated type.

A typical range address is a string that defines a rectangle of cells.  It includes dollar signs ($) and a colon (:) to separate columns and rows, and to separate the top-left cell from the bottom-right cell.  For example, $A$4:$C$15 is a range that starts at A4 and ends at C15.  In the function below, the $ and : are used as position markers to extract row and column values.

The enumerated type is dimensioned in the declarations section of the code module.  In the function, the ReturnType argument is defined as the custom type, XAddressType.  This lets us take advantage of the IntelliSense features of the VBA editor.  When we call the function, it will give us the list of enum constants to choose from like this:

CustomEnums

Code:

' Define enums for XAddress.
Public Enum XAddressType
    xatFirstRow
    xatLastRow
    xatFirstColumn
    xatLastColumn
End Enum

Public Function XAddress(RangeAddress As String, ReturnType As XAddressType)
On Error GoTo Err_XAddress
' This function returns a part of the RangeAddress, specified by ReturnType.
' It expects a RangeAddress in a format like "$A$2:$C$15", but just the
' address to the left of the colon will work.  The $ are required.

' For example, to get the last column of a selected range, you may use:
' XAddress(ActiveWindow.RangeSelection.Address, xatLastColumn)

' XAddress() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

    Dim intPos As Integer
    Dim intPosColon As Integer
    Dim intPosFinal As Integer

    Select Case ReturnType
    Case xatFirstRow
        ' Look for the colon.
        intPosColon = InStr(1, RangeAddress, ":")
        ' If there is no colon then use the length plus one.
        If intPosColon = 0 Then intPosColon = Len(RangeAddress) + 1
        ' Look for the second $.
        intPos = InStr(2, RangeAddress, "$")
        ' Get the number between the two positions.
        XAddress = Mid(RangeAddress, intPos + 1, intPosColon - intPos - 1)

    Case xatLastRow
        ' Get the last $.  Then anything to the right of that is the
        ' last row number.
        intPos = InStrRev(RangeAddress, "$")
        XAddress = Right(RangeAddress, Len(RangeAddress) - intPos)

    Case xatFirstColumn
        ' Look for the second $.
        intPos = InStr(2, RangeAddress, "$")
        ' The first column is between the first digit and the second $.
        XAddress = Mid(RangeAddress, 2, intPos - 2)

    Case xatLastColumn
        ' Look for the colon.
        intPosColon = InStr(1, RangeAddress, ":")
        ' Look for the fourth $.
        intPos = InStr(intPosColon + 2, RangeAddress, "$")
        ' Get the number between the two positions.
        XAddress = Mid(RangeAddress, intPosColon + 2, intPos - intPosColon - 2)

    End Select

Exit_XAddress:
    On Error Resume Next
    Exit Function

Err_XAddress:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "XAddress()"
    XAddress = False
    Resume Exit_XAddress

End Function

Download Code:

basXAddress.zip