0

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
Community
  • 1
  • 1
DannyP
  • 25
  • 6
  • 2
    You can greatly tighten up the code by [avoiding the use of `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), FYI. – BruceWayne Apr 28 '17 at 19:59

4 Answers4

4

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Matthew Hostetler
  • 174
  • 5
1

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(...).

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    Upvoted, for inverting the (intended) condition and making it easier to read by eliminating the negatives. Note that calls to `Cells` and `Rows` implicitly refer to `ActiveSheet` though. – Mathieu Guindon Apr 28 '17 at 20:18
  • @Mat'sMug - Thanks. And yeah, I know I didn't qualify the ranges. OP posted some very simple code, using `ActiveCell`, so I figured I'd just keep my answer simple. There's a way I can make it a little "tighter" too, but figure OP is learning and this should get them on their way! – BruceWayne Apr 28 '17 at 20:35
0

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.

Boeryepes
  • 506
  • 1
  • 3
  • 7
0

I am using something similar that looks like:

Dim RowStaff as Integer
RowStaff = 1
While (.Cells(RowStaff + RowHeader, ColStatus) <> "") Or (.Cells(RowStaff + RowHeader + 1, ColStatus) <> "")

'Code goes here

RowStaff=RowStaff+1

Wend

Where RowHeader, ColStatus are both constants

Obviously (hopefully) the names I chose are specific to my task, but adjust/omit as necessary...

Amelia
  • 1