Excel VBA: Last Used Column

Similar to my previous post on finding the last used row in a sheet, this is a function that can be used to find the last used column. These two functions work nicely together to limit a loop or define a range when the source data may vary in size.

The function: getLastCol():

• If called with no parameters, the last used column in the active worksheet will be returned.
• The optional sheetName parameter lets you reference a sheet other than the one that is active.
• The optional rowNum parameter returns the last used column in a particular row.
• The optional colLimit parameter limits the total number of iterations in the loop when searching for the last used column, this can be used to speed up execution when you have an idea of what the sheet may contain already.
• If there is an error with the parameters it will return zero

Examples:

getLastCol() = 3
getLastCol("Fruit") = 3
getLastCol(,3) = 2 'last used column on row 3 only
getLastCol(Vegetables) = 0 'this sheet name does not exist

The Code:

Function getLastCol(Optional sheetName As String, Optional rowNum As Long, Optional colLimit As Long) As Long
'by Elliot 7/22/20 www.elliotmade.com
'this function will return the last used column on a sheet in a single row
'if no sheet name is specified it will use the active sheet
'if no row is specified it will find the last column in any row up to an optional limit (for faster performance)
'two assumptions are made: the file type is .xlsx or similar that supports 16k columns
'and the sheet is in the active workbook
'a zero returned means that there was a failure

Dim i As Long
Dim j As Long
Dim curLastCol As Long

'check for valid inputs first, return zero if there is a problem
If rowNum < 0 Or rowNum > 1048575 Then GoTo abort

If sheetName <> "" Then
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sheetName Then GoTo sheetOK
    Next i
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

If colLimit = 0 Then
    colLimit = 16384
ElseIf colLimit < 0 Or colLimit > 16384 Then
    GoTo abort
End If

If sheetName = "" Then sheetName = ActiveSheet.Name

'if no problem, find the last column
If rowNum = 0 Then
    getLastCol = Worksheets(sheetName).Cells(1, 16384).End(xlToLeft).Column
    For j = 1 To colLimit
        If Worksheets(sheetName).Cells(1048575, j).End(xlUp).Row > 1 Then curLastCol = j
        If curLastCol > getLastCol Then getLastCol = curLastCol
    Next j
Else
    getLastCol = Worksheets(sheetName).Cells(rowNum, 16384).End(xlToLeft).Column
End If

abort:

End Function

You may notice that the method using xlToLeft is used to find the last column in a specific row, but I did not use it when searching for the last column in the entire sheet; to do this in a loop would require iterating through every single row (over 1M!) and would take a significant amount of time. I chose to do two things to shortcut this process: first, check the first row because it often has headings for the rest of the document, and second, find the last used row in every column instead. This limits the possible trips through the loop to 16k at most (the number of columns in a sheet). I have some other ideas to improve on this, but I’ll leave it alone for now until it becomes a bottleneck.

Next I’ll post an example that puts this function to use, stay tuned.