tags:

views:

6923

answers:

7

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

A: 
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
Galwegian
+2  A: 
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")
GSerg
+2  A: 

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
Remou
This function will return wrong results when:- strColumn is a number- there is some data in row 65536- you are using Excel 2007 with more than 65536 rows
GSerg
A: 

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)
databyss
A: 

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

Jon Fournier
A: 
Public Function LastData(rCol As Range) As Range

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

End Function

Usage: ?lastdata(activecell.EntireColumn).Address

Dick Kusleika
A: