For Each loop weakens

[chabuca1]

Hello,

I do not seem to be having luck with this vb script in excel:

Sub DeleteRecords()
Dim delCell As Range
For Each delCell In Worksheets("Sheet1").Range("B2:B6")
If delCell.Value = "Del" Then
delCell.EntireRow.Delete
End If
Next delCell
End Sub

I think my "group" (worksheets) is somehow wrong because it will work for a couple of rows (i.e. delCell is each cell searched down B:B for "del"), then it exits! I would greatly appreciate advice if someone knows. Thank you and best regards.

[gwdash]

It's because your modifing a collection your looping through, it's like your changing the i in a regualar For...Next... loop

This code should fix that(by moving the stuff into a traditonal collection first).

Sub DeleteRecords()
Dim delCell As Range
Dim colCells As New Collection
For Each delCell In ActiveSheet.Range("B:B")

If delCell.Value = "Del" Then
colCells.Add delCell
End If
Next delCell

For Each delCell In colCells
    delCell.EntireRow.Delete
Next
Set colCells = Nothing

End Sub

[TheOnly]

Hi gwdash !!!

You code make the same like chabuca1 one. The only difference is that zou need a collection too.

The problem is that if you delete 1 row eg. row 3 then
excel shift up the rows - so row 4 becomes row 3 and so on
Therefor you run in the same problem with your collection.

In my opinion u should an array to store the cells which
you want to delete and use then a for next loop from top of the array to the bottom.

If you need a code example -pls reply

-cu TheOnly

[chabuca1]

Thank you GwDash and TheOnly for your directions. I will try it to determine it right away and post back when I get it! Thank you again!