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

Question

How can I find the last row that contains data in a specific column and on a specific sheet?

Answer 1

Function LastRow(rng As Range) As Long
    Dim iRowN As Long
    Dim iRowI As Long
    Dim iColN As Integer
    Dim iColI As Integer
    iRowN = 0
    iColN = rng.Columns.count
    For iColI = 1 To iColN
        iRowI = rng.Columns(iColI).Offset(65536 - rng.Row, 0).End(xlUp).Row
        If iRowI > iRowN Then iRowN = iRowI
    Next
    LastRow = iRowN
End Function

Answer 2

function LastRowIndex(byval w as worksheet, byval col as variant) as long
  dim r as range

  set r = application.intersect(w.usedrange, w.columns(col))
  if not r is nothing then
    set r = r.cells(r.cells.count)

    if isempty(r.value) then
      LastRowIndex = r.end(xlup).row
    else
      LastRowIndex = r.row
    end if
  end if
end function

Usage:

? LastRowIndex(ActiveSheet, 5)
? LastRowIndex(ActiveSheet, "AI")

Answer 3

How about:

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

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

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

Answer 4

First function moves the cursor to the last non-empty row in the column. Second Function prints that columns row.

Selection.End(xlDown).Select
MsgBox (ActiveCell.Row)

Answer 5

You should use the .end(xlup) but instead of using 65536 you might want to use:

sheetvar.Rows.Count

That way it works for Excel 2007 which I believe has more than 65536 rows

Answer 6

Public Function LastData(rCol As Range) As Range

Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)

End Function

Usage: ?lastdata(activecell.EntireColumn).Address