Loop will not Continue

[MJA001]

I can't figure out why the code below will not loop. It's supposed to go through all the rows in the spread sheet and delete any rows that say "Yes". But currently it will delete the first row that has a "yes", then it stops. No error, it just stops. I'm guessing it has something to do with deleting the same range I'm validating with my If/ Then statement, but I don't know what to do to fix it. Any Ideas?

Dim TAMARCHIVE As Range

For Each TAMARCHIVE In Sheets("Sheet1").Range("B4", Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp))
If TAMARCHIVE = "Yes" Then
TAMARCHIVE.EntireRow.Delete

End If
Next TAMARCHIVE

End Sub

 

[Fluff]

When deleting/inserting rows you need to work from the bottom up.

Dim i As Long

For i = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row To 4 Step -1
   If Range("B" & i).Value = "Yes" Then Rows(i).Delete
Next i

End Sub

 

[MJA001]

That worked perfectly! Thanks Fluff!

 

[Fluff]

Glad to help & thanks for the feedback