Determine when two consecutive cells are blank

Question

The following While loop is meant to iterate down a column until two consecutive blank cells are found. It exits as soon as curCell is empty, disregarding the value in lastCell. While debugging, I have verified that lastCell has a value and is not empty, but the loop still exits. Something wrong with my And statement?

While (lastCell <> "") And (curCell <> "")
    lastCell = curCell
    ActiveCell.Offset(1, 0).Select
    curCell = ActiveCell
Wend

Answer 1

You should use Or instead of And.

And requires both of those statements to be true in order for the while loop to continue. If one of those statements is false (if one cell is empty), the while loop will exit.

Using Or, the while statement will continue until both cells are blank.

Answer 2

If I may, this is a better way to do what you're trying, since it looks from the second row all the way until the last row. It'll stop when it finds the first two cells that are both empty.

Sub findTwoEmptyCells()
Dim lastRow As Long, i As Long
Dim firstEmptyCell As Range

lastRow = Cells(Rows.Count, 1).End(xlUp).Row ' Assuming your column A has the most data or is the row you want to check.

For i = 1 To lastRow
    If Cells(i, 1).Value = "" And Cells(i, 1).Offset(1, 0).Value = "" Then
        Set firstEmptyCell = Cells(i, 1)
        Exit For
    End If
Next i

'Now, you have a cell, firstEmptyCell, which you can do things with, i.e.
firstEmptyCell.Value = "Empty!"

End Sub

Edit: Also, in the even there are no two empty cells in a row, add this before firstEmptyCell.Value:

If firstEmptyCell Is Nothing Then ' If there are no two empty cells in a row, exit the sub/do whatever you want
    MsgBox ("There are no two empty cells in a row")
    Exit Sub
End If

Edit2: As @MatsMug points out, this will work fine assuming you don't change/use multiple worksheets. If you do, add the sheet name before Cells(), i.e. Sheets("Sheet1").Cells(...).

Answer 3

In addition to the other comments to improve the code, your original logical check: While (lastCell <> "") And (curCell <> "") should have been: While Not (lastCell = "" And curCell = "") because that way the loop runs until both last and current are empty, which is what you were looking for.