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.

When you delete rows you should always loop from bottom to top. — Patrick Honorez
– Patrick Honorez, Commented Dec 18, 2017 at 16:26
and I wouldn't name a variable cell which is probably a reserved word — Patrick Honorez
– Patrick Honorez, Commented Dec 18, 2017 at 16:29
@PatrickHonorez cell is not a reserved word in vba. I can't find any source for this from Microsoft, but here is what I found with a quick google search — Taelsin
– Taelsin, Commented Dec 18, 2017 at 16:39
@PatrickHonorez that is an extremely naive and inefficient idea. Modifying the collection you're iterating is terrible advice in any language, VBA isn't any different. Also if cell was illegal, the code wouldn't compile. VBA isn't only hosted in Excel; not all VBA host applications have cells. cell being "reserved" makes no sense. — Mathieu Guindon
– Mathieu Guindon, Commented Dec 18, 2017 at 18:18

Mathieu Guindon · Accepted Answer · 2017-12-18 17:08:35Z

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.

pokemon_Man · Accepted Answer · 2017-12-18 16:28:00Z

1

try this, it will loop from row 25 to 11 backwards and find anything not "Total"

Dim i As Integer
For i = 25 To 11 Step -1 ' change to whatever row you want
    If Range("B" & i) <> "Total" Then
        Range("B" & i).EntireRow.Delete
    End If
Next

answered Dec 18, 2017 at 16:28

pokemon_Man

9021 gold badge11 silver badges25 bronze badges

2 Comments

NCHBanna Over a year ago

Thanks this work. However how do I reverse loop if I don't know where my data ends in that column. Data always starts in B11. I tried using VBA's .end function but couldn't get it to work.

SilentRevolution Over a year ago

If your data always starts in B11 and is continuous to the end (no empty rows) then use For i = 11 to Cells(11,2).End(xlDown).Row Step -1 As a side note, this, and the rest of the code, assumes ActiveSheet which could be a bad idea if you have multiple sheets or workbooks which could be the active one.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related