How can I find last row that contains data in the Excel sheet with a macro?

Accepted answer +30 upvotes

How about:

 Sub GetLastRow(strSheet, strColum)
 Dim MyRange As Range
 Dim lngLastRow As Long

    Set MyRange = Worksheets(strSheet).Range(strColum & "1")

    lngLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
 End Sub

Re Comment

This

  Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Will return the row number of the last cell even when only a single cell in the last row has data.