How to Determine The Last Used Column in a Row in Excel

A while back, I demonstrated how easy it was to determine the last used row in a given Excel column:

MS Excel - VBA - Determine Last Row

I had been needing to find an easy way to determine the last used row in a given column, or possibly the next available row in a column. I search Google and found numerous examples, which I used for a short period of time. Code such as: Range("A65536").End(xlup).Select But that code was not 100% reliable and I wanted, as much as possible, to avoid useless .Select statement to try and optimize my code as best I could. Well, I finally managed to get it down to a single line of code without the use of any .Select statements and which…

Continue reading →

I realized that I never provided the equivalent code to determine the last used column in a given row, which can be equally useful.

Originally I started out following a very similar concept as with the last row procedure, but this time instead of going to the End in the vertical direction, we go to the End in the horizontal direction.

However, work on a later project permitted me to discovered that the approach did not work when columns within the row were merged and thus I ended up re-working the approach completely. It isn’t as elegant, but does work.

Ultimately, giving me a function like:

'---------------------------------------------------------------------------------------
' Procedure : GetLastUsedColumnInRow
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the last used Column in the specified Row
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oWs       : Worksheet (object) to search in
' lRowNo    : The row number to find the last used column in
'
' Usage:
' ~~~~~~
' GetLastUsedColumnInRow(ActiveSheet, 517)
'   Returns -> 6
'
' GetLastUsedColumnInRow(WorkSheets("Sheet2"), 4)
'   Returns -> 6
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2011-05-09              Initial Release
' 2         2014-09-17              Changed technique to deal with merged rows in row!!!
' 3         2024-11-28              Initial Public Release
'                                   Renamed function
'---------------------------------------------------------------------------------------
Function GetLastUsedColumnInRow(oWs As Excel.Worksheet, _
                                lRowNo As Long) As Long
    Dim oUsedRange            As Excel.Range
    Dim oCell                 As Excel.Range
    Dim lLastCol              As Long
    Dim lCounter              As Long

    On Error Resume Next
    Set oUsedRange = oWs.usedRange
    On Error GoTo Error_Handler

    If oUsedRange Is Nothing Then
        GetLastUsedColumnInRow = 0
        Exit Function
    End If

    ' Work backwards throught the used columns in the row
    For lCounter = oUsedRange.Columns.Count To 1 Step -1
        Set oCell = Cells(lRowNo, lCounter)
        If oCell.MergeCells Then
            If Not IsEmpty(Cells(oCell.mergeArea.Row, lCounter).Value) Then
                lLastCol = lCounter
                Exit For
            End If
        Else
            If Not IsEmpty(oCell.Value) Then
                lLastCol = lCounter
                Exit For
            End If
        End If
    Next lCounter

    GetLastUsedColumnInRow = lLastCol

Error_Handler_Exit:
    On Error Resume Next
    Set oCell = Nothing
    Set oUsedRange = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetLastUsedColumnInRow" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

You’ll also notice I included a WorkSheet input argument making this more flexible.

Usage Examples

Now, when you which to determine the last used column for a row, you simply do:

Debug.Print GetLastUsedColumnInRow(ActiveSheet, 517)

Debug.Print GetLastUsedColumnInRow(WorkSheets("Sheet2"), 4)

which will return a numeric value representing the last used column, say, 6 (which is column F in alphabetic representation).

So if you wanted to select the last column in row 12, you could do:

ActiveSheet.Cells(12, GetLastUsedColumnInRow(ActiveSheet, 12)).Select