Why is simple loop macro stopping

[nicolewh]

I have a macro that stops when it encounters the first cell which meets the condition of the if then statement. Why?

Sub Macro1()
Dim Rng As Range
Dim MyRow As Range
Set Rng = Range("Table1[[column1]]")
For Each MyRow In Rng
    Ctw = UBound(Split(MyRow.Value, " ")) + 1
           If Ctw < 3 Then   
                   MyRow.EntireRow.Delete         
           End If
    Next MyRow
End Sub

Thanks!

Nicole

 

[mikerickson]

I'm not sure that it would stop at the first cell that it finds, but I am sure that it will not work properly.

As you loop through Rng, you are going from top to bottom. When you delete a row, you are moving the un-examined cells below and the next loop skips the row that moved into the vacated space.

To see what I mean, run this code

Dim aCell as Range
For each aCell in Range("A1:A10")
    MsgBox aCell.Address
    aCell.EntireRow.Delete
Next aCell

you will see only 5 message boxes. And there will be cells that are not deleted.


The fix is to work from bottom to top.

Sub Macro1()
    Dim Rng As Range
    Dim MyRow As Range
    Dim i As Long

    Set Rng = Range("Table1[[column1]]")

    For I = Rng.Rows.Count To 1 Step-1 
        Set myRow = Rng.Cells(i, 1)
        Ctw = UBound(Split(MyRow.Value, " ")) + 1
        If Ctw < 3 Then   
            MyRow.EntireRow.Delete         
        End If
    Next MyRow
End Sub

 

[pedie]

This works well for me....

 

[nicolewh]

Me too!
Thanks so much

Nicole