eirikdaude
Board Regular
- Joined
- Nov 26, 2013
- Messages
- 93
- Office Version
- 365
- Platform
- Windows
Given the following:
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:
- 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
VBA Code:
' 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