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