excel-vba Find the Last Non-Empty Column in Worksheet


Example

Private Sub Get_Last_Used_Row_Index()
    Dim wS As Worksheet
    
    Set wS = ThisWorkbook.Sheets("Sheet1")
    Debug.Print LastCol_1(wS)
    Debug.Print LastCol_0(wS)
End Sub

You can choose between 2 options, regarding if you want to know if there is no data in the worksheet :

  • NO : Use LastCol_1 : You can use it directly within wS.Cells(...,LastCol_1(wS))
  • YES : Use LastCol_0 : You need to test if the result you get from the function is 0 or not before using it
Public Function LastCol_1(wS As Worksheet) As Double
    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastCol_1 = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
        Else
            LastCol_1 = 1
        End If
    End With
End Function

The Err object's properties are automatically reset to zero upon function exit.

Public Function LastCol_0(wS As Worksheet) As Double
    On Error Resume Next
    LastCol_0 = wS.Cells.Find(What:="*", _
                            After:=ws.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
End Function