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.

Answers


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

Need Your Help

Neo4j - Unable to create Relationship entities

java neo4j spring-data-neo4j

I am trying to insert the relationships between two nodes in Neo4j. I am using the Neo4J(2.1.8 Community) & spring-data-neo4j(3.3.0.RELEASE).

selecting hash values by value property in ruby

ruby arrays algorithm performance hash

In Ruby, I have a hash of objects. Each object has a type and a value. I am trying to design an efficient function that can get the average of the values of all of objects of a certain type within ...