Optimizing function for returning the last used row in a given range

[eirikdaude]

Given the following:

• I have a range in the middle of some other data, in which I want to find the last used row
• The range will only contain numbers
• The last used row is defined as the row with the highest row number, in which any data is entered

I cobbled together the following function:

' 09. Returns 0 if no rows are in use
Function last_used_row_in_range(r As Range) As Long
    Dim i As Long
   
    For i = r.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.Sum(r.Rows(i)) <> 0 Then
            last_used_row_in_range = r.Cells(i, 1).Row
            Exit For
        End If
    Next i
End Function ' last_used_row_in_range

Sub test()
    Debug.Print last_used_row_in_range(ActiveWorkbook.Worksheets("36").Range("E4:K21"))
End Sub

The spreadsheet I'll use the function on will look something like this:

Now the function works well enough for my special case, but it has a few weaknesses:

• If a 0 is entered into a row, the function won't register the the row as used - this is however something which is unlikely to happen in the spreadsheet I am writing the code for
• I'm not sure if it is particularly efficient, but considering it won't be used on any very large ranges it might not matter

Anyway, my question is: Are there any simple optimizations I can make for the function, and is there any simple way I can make it more robust?

 

[cmowla]

Here are two methods that are not sensitive to just numbers, but any characters (including white spaces). The first is longer (and loops), but it doesn't use Range.Find. (And that's desirable by some.)

Sub Test__Last_Used_Row()
MsgBox Last_Used_Row(Sheets(ActiveSheet.Name).Range("A4:G20"))
End Sub
Function Last_Used_Row(r As Range)

Dim c As Integer, lastRow As Integer
lastRow = 0
For c = 1 To r.Columns.Count
    If lastRow < r.Parent.Cells(Rows.Count, c).End(xlUp).Row Then lastRow = r.Parent.Cells(Rows.Count, c).End(xlUp).Row
Next c
Last_Used_Row = lastRow

End Function

Sub Test__Last_Used_Row2()
MsgBox Last_Used_Row2(Sheets(ActiveSheet.Name).Range("A4:G20"))
End Sub
Function Last_Used_Row2(r As Range)
Last_Used_Row2 = r.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
End Function

And here is a 3rd way that uses dynamic array formulas (since you have 365):

Sub Test__Last_Used_Row3()
MsgBox Last_Used_Row3(Sheets(ActiveSheet.Name).Range("A4:G20"))
End Sub
Function Last_Used_Row3(r As Range)
Last_Used_Row3 = Evaluate("MAX(IF(" & r.Parent.Name & "!" & r.Address & "<>" & Chr(34) & Chr(34) & ",ROW(" & r.Parent.Name & "!" & r.Address & "),0))")
End Function

 

[eirikdaude]

Here are (...)

Thanks. I figured there'd be a better way to do it - thanks for finding a few

I went with a variation on your third method.