CODEWITHSUNDEEP
excelvba
Ken
Ken

Reputation: 3141

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.

Upvotes: 0

Views: 831

Answers (2)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 1

whytheq
whytheq

Reputation: 35557

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":

enter image description here

Then if some extra rows are inserted I can still get to the Totals row ok:

enter image description here

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

Upvotes: 0

Related Questions