Find last row in range

There are mulitple results and methods when searching for the LastRow (in Column B).

When using Cells(.Rows.Count, "B").End(xlUp).Row you will get the last row with data in Column B (it ignores rows with spaces, and goes all the way down).

When using:

 With wbshtSelect.Range("B10").CurrentRegion
     LR_wbSelectNew = .Rows(.Rows.Count).Row
 End With

You are searching for the last row with data in Column B of the CurrentRegion, that starts from cell B10, untill the first line without data (it stops on the first row with empty row).

Full Code:

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B
With wbshtSelect
    LR_wbSelectNew = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >>result 31

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >> result 18

End Sub

Edit1: code searches for last row for cells with values (it ignores blank cells with formulas inside).

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With

Dim Rng         As Range    
Set Rng = wbshtSelect.Range("B10:B" & LR_wbSelectNew)

' find last row inside the range, ignore values inside formulas
LR_wbSelectNew = Rng.Find(What:="*", _
                    After:=Range("B10"), _
                    LookAt:=xlPart, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

' for debug
Debug.Print LR_wbSelectNew  ' << result 18 (with formulas in the range)

End Sub

Hope this piece of code helps !

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

I came here looking for a way to find the last row in a non-contiguous range. Most responses here only check one column at a time so I created a few different functions to solve this problem. I will admit, though, that my .Find() implementation is essentially the same as Shai Rado's answer.

Implementation 1 - Uses Range().Find() in reverse order

Function LastRowInRange_Find(ByVal rng As Range) As Long

    'searches range from bottom up stopping when it finds anything (*)
    Dim rngFind As Range
    Set rngFind = rng.Find( What:="*", _
                            After:=Cells(rng.row, rng.Column), _
                            LookAt:=xlWhole, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious)

    If Not rngFind Is Nothing Then
        LastRowInRange_Find = rngFind.row
    Else
        LastRowInRange_Find = rng.row
    End If

End Function

Implementation 2 - Uses Range().End(xlUp) on each column

Function LastRowInRange_xlUp(ByVal rng As Range) As Long

    Dim lastRowCurrent As Long
    Dim lastRowBest As Long

    'loop through columns in range
    Dim i As Long
    For i = rng.Column To rng.Column + rng.Columns.count - 1
        If rng.Rows.count < Rows.count Then
            lastRowCurrent = Cells(rng.row + rng.Rows.count, i).End(xlUp).row
        Else
            lastRowCurrent = Cells(rng.Rows.count, i).End(xlUp).row
        End If

        If lastRowCurrent > lastRowBest Then
            lastRowBest = lastRowCurrent
        End If
    Next i

    If lastRowBest < rng.row Then
        LastRowInRange_xlUp = rng.row
    Else
        LastRowInRange_xlUp = lastRowBest
    End If

End Function

Implementation 3 - Loops through an Array in reverse order

Function LastRowInRange_Array(ByVal rng As Range) As Long

    'store range's data as an array
    Dim rngValues As Variant
    rngValues = rng.Value2

    Dim lastRow As Long

    Dim i As Long
    Dim j As Long

    'loop through range from left to right and from bottom upwards
    For i = LBound(rngValues, 2) To UBound(rngValues, 2)                'columns
        For j = UBound(rngValues, 1) To LBound(rngValues, 1) Step -1    'rows

            'if cell is not empty
            If Len(Trim(rngValues(j, i))) > 0 Then
                If j > lastRow Then lastRow = j

                Exit For
            End If

        Next j
    Next i

    If lastRow = 0 Then
        LastRowInRange_Array = rng.row
    Else
        LastRowInRange_Array = lastRow + rng.row - 1
    End If

End Function

I have not tested which of these implementations works fastest on large sets of data, but I would imagine that the winner would be _Array since it is not looping through each cell on the sheet individually but instead loops through the data stored in memory. However, I have included all 3 for variety :)

---

How to use

To use these functions, you drop them into your code sheet/module, specify a range as their parameter, and then they will return the "lowest" filled row within that range.

Here's how you can use any of them to solve the initial problem that was asked:

Sub answer()

    Dim testRange As Range
    Set testRange = Range("A1:F28")

    MsgBox LastRowInRange_Find(testRange)
    MsgBox LastRowInRange_xlUp(testRange)
    MsgBox LastRowInRange_Array(testRange)

End Sub

Each of these will return 18.