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