Find last row containing data in a specific range in excel

Try this -

   Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

Dim r As Range

Set r = Range("A5:A20")
MsgBox Range(r.Address).Rows.Count - Range(r.Address).Row

Select allOpen in new window

Are you looking for VBA solution or a Excel Formula?

Excel formula would be -

=LOOKUP(2,1/(A:A<>""),ROW(A:A)) - This returns the Row number

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) - This returns the Value in the cell

Ron de Bruin have  a couple of possible solutions:
https://www.rondebruin.nl/win/s9/win005.htm

You can utilize the following Function to find the last row with data within a range.

Function LastRow(rng As Range) As Long
If rng.Cells(rng.Cells.Count) <> "" Then
    LastRow = rng.Cells(rng.Cells.Count).Row
Else
    LastRow = rng.Cells(rng.Cells.Count).End(xlUp).Row
End If
If LastRow < rng.Cells(1).Row Then MsgBox rng.Address & " is empty."
End Function

Select allOpen in new window

Then you can use it like this...

Sub Test()
Dim lr As Long
lr = LastRow(Range("A5:A20"))
MsgBox lr
End Sub

Select allOpen in new window

If rng.Cells(rng.Cells.Count) <> "" Then

Better use the vbNullString constant, it give more meaning to the code.

@Subodh Tiwari (Neeraj)

How do you have it ignore formulas

You may try something like this...

Function LastRow(rng As Range) As Long
    LastRow = Cells(Application.Evaluate("MAX(IF(" & rng.Address & "<>"""",ROW(" & rng.Address & ")),0,1)"), Split(rng.Address, "$")(1)).Row
End Function

Select allOpen in new window

Sub Test()
Dim lr As Long
lr = LastRow(Range("A5:A20"))
MsgBox lr
End Sub

Select allOpen in new window

Question:

What if the range span over several columns ?

This is from an article I found years ago.

Last Row 1
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn’t keep track of the last cell in a very adequate form.

Last Row 2
Another method to find the last used row in a particular column is:

Dim lngLastRow as Long
Dim lngRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row

Last Row 3
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

Last Row 4
but that doesn’t tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Last Row 5


LastRow = ActiveSheet.UsedRange.Rows.Count

Last Row 6
If all else fails
    lngLastCol = .Cells(14, Columns.Count).End(xlToLeft).Column
    lngLastRow = .UsedRange.Rows.Count '.Range("C" & Rows.Count).End(xlUp).Row
    For lngRow = lngLastRow To 14 Step -1
        If Application.WorksheetFunction.Count(Range(.Cells(lngRow, "C"), .Cells(lngRow, lngLastCol))) > 0 Then
            lngLastRow = lngRow
            Exit For
        End If
    Next

In a formula
=MAX(INDEX((A:A<>””)*ROW(A:A),0))