How to find the first empty row in Excel when subsequent rows are not empty
How do I find the first completely empty row on a particular worksheet looking top down?
Because the sheet includes a Totals row at the end of several empty rows, I don't think I can simply use the Range.End property and offset by 1.
To find the first blank row, loop through the actual UsedRange determined by Find using CountA to test for a blank row
If no row is found, use the next row after the end of the last cell determined by the Find
Sub Method2() Dim rng1 As Range Dim rng2 As Range Dim bFound As Boolean Set rng2 = ActiveSheet.Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) For Each rng1 In Range([a1], Cells(rng2.row, "A")) If Application.WorksheetFunction.CountA(Rows(rng1.row)) = 0 Then MsgBox rng1.row & " is the first blank row" bFound = True Exit For End If Next rng1 If Not bFound Then MsgBox rng2.Offset(1, 0).row & " is the first blank row" End Sub
forgive me if I've misinterpreted the question.
Totals is always the last row and you'd therefore like to find the next row. I assume that Totals changes the row it is located on and there are blank rows in front of it.
Why does the location of Totals change?
I'd just make cell where totals is located a named range "Totals":
Then if some extra rows are inserted I can still get to the Totals row ok:
So in VBA its then trivial:
Sub Method2() MsgBox "Total named range is " & ActiveSheet.Range("Totals").Address(0, 0) MsgBox "Next empty row is " & ActiveSheet.Range("Totals").Row + 1 End Sub