Get the last column

This is the function l have been using for many years to return the last row.
It suits most of my needs as the parameters allow you to specify:
1. Workbook
2. Worksheet
3. Row
If you omit the row it will return the last column used in the specified sheet
Author: michael@excelexperts.co.uk

Function LC(Optional WorkbookName As String, Optional WorksheetName As String, Optional SpecificRow As Long)

    Dim wb As Workbook
    Dim ws As Worksheet

    If WorkbookName = vbNullString Then WorkbookName = ThisWorkbook.Name
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name

    Set wb = Workbooks(WorkbookName)
    Set ws = wb.Worksheets(WorksheetName)

    On Error GoTo SheetEmpty
    If SpecificRow < 1 Then
        With ws
            LC = .Cells.Find("*", .Cells(1), xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
        End With
    Else
        With ws
            LC = .Cells(SpecificRow, .Columns.Count).End(xlToLeft).Column
            If IsEmpty(.Cells(SpecificRow, LC).Value) Then GoTo SheetEmpty
        End With
    End If
    Exit Function

SheetEmpty:
    LC = 1

End Function

Address

Brookfield,
55 Heath Drive
Brookwood, Surrey
GU24 0HQ England

Scroll to Top