vba-find-the-last-column-on-a-worksheet.vb

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT         : Sheet, the worksheet we'll search to find the last column
'OUTPUT        : Long, the last occupied column
'SPECIAL CASE  : if Sheet is empty, return 1
'EXAMPLES BELOW:
'
'assume that there is a single entry on MySheet in cell C3
'LastColNum(MySheet)
'>> 3
'
'assume that EmptySheet is totally empty
'LastColNum(EmptySheet)
'>> 1
'
Public Function LastColNum(Sheet As Worksheet) As Long
    With Sheet
        If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
            LastColNum = Sheet.Cells.Find(What:="*", _
                            After:=.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
        Else
            LastColNum = 1
        End If
    End With
End Function