Why is simple loop macro stopping

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
I have a macro that stops when it encounters the first cell which meets the condition of the if then statement. Why?

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,017
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
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,870
Messages
5,655,712
Members
418,232
Latest member
Hey Nola

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top