Better way to find last used row

Solution 1:

You should use a with statement to qualify both your Rows and Columns counts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.

Last Column

With Sheets("Sheet2")
    .Cells(1, .Columns.Count).End(xlToLeft).Column
End With 

Last Row

With Sheets("Sheet2")
    .Range("A" & .Rows.Count).End(xlUp).Row
End With 

Or

With Sheets("Sheet2")
    .Cells(.Rows.Count, 1).End(xlUp).Row
End With 

Solution 2:

How is this?

dim rownum as integer
dim colnum as integer
dim lstrow as integer
dim lstcol as integer
dim r as range

'finds the last row

lastrow = ActiveSheet.UsedRange.Rows.Count

'finds the last column

lastcol = ActiveSheet.UsedRange.Columns.Count

'sets the range

set r = range(cells(rownum,colnum), cells(lstrow,lstcol))

Solution 3:

This function should do the trick if you want to specify a particular sheet. I took the solution from user6432984 and modified it to not throw any errors. I am using Excel 2016 so it may not work for older versions:

Function findLastRow(ByVal inputSheet As Worksheet) As Integer
    findLastRow = inputSheet.cellS(inputSheet.Rows.Count, 1).End(xlUp).Row
End Function

This is the code to run if you are already working in the sheet you want to find the last row of:

Dim lastRow as Integer
lastRow = cellS(Rows.Count, 1).End(xlUp).Row