excel - For Each loop won't delete all rows with specific values

Question

I want to delete all rows that do not contain the value "Total" in Range("B11:B25").

Below is my code.

Dim cell As Range

For Each cell In Range("B11:B25")
    If cell.Value <> "Total" Then
    cell.EntireRow.Delete
End If

Next

End Sub

Above code will only delete some rows with cells that do not have the value "Total". If I have to delete all rows that do not contain "Total", I will have to run this multiple times which is not practical.

Answer 1

Modifying a collection you're iterating is always a bad idea. Sure you could start at the bottom and call it a day, but then your next question is going to be "my code is painfully slow, how do I make it faster?"

Have a CombineRanges function responsible for Union-ing ranges:

Private Function CombineRanges(ByVal source As Range, ByVal toCombine As Range) As Range
    If source Is Nothing Then
        Set CombineRanges = toCombine
    Else
        Set CombineRanges = Union(source, toCombine)
    End If
End Function

Now, change your loop so that instead of deleting rows, it determines what rows need to be removed:

Dim toDelete As Range
Dim cell As Range
For Each cell In ActiveSheet.Range("B11:B25")
    If cell.Value <> "Total" Then Set toDelete = CombineRanges(toDelete, cell)
Next

If Not toDelete Is Nothing Then toDelete.EntireRow.Delete

And now you have an efficient loop (always iterate object collections with a For Each loop) that doesn't modify the object collection it's iterating, does only one thing, and you have a single Delete operation going on, which will only trigger a single worksheet Changed event, one single recalculation, and will perform well regardless of whether you're deleting 20 or 2000 rows.