对于每个循环不会删除具有特定值的所有行 [英] For Each loop won't delete all rows with specific values

问题描述

我要删除Range("B11:B25")中所有不包含"Total"值的行.

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

下面是我的代码.

Dim cell As Range

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

Next

End Sub

上面的代码将只删除单元格中没有"Total"值的某些行.如果必须删除所有不包含总计"的行，则必须多次运行，这不切实际.

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.

推荐答案

修改要迭代的集合始终是一个坏主意.当然，您可以从最底层开始命名，但是接下来的问题将是我的代码非常缓慢，如何使它更快?"

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?"

具有负责Union ing范围的CombineRanges函数:

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

现在您有了一个有效的循环(始终使用For Each循环迭代对象集合)，该循环不会修改它正在迭代的对象集合，它只做一件事，并且您只有一个Delete操作正在进行中，这样只会触发一个工作表Changed事件，一次重新计算，并且无论您要删除20行还是2000行，其效果都很好.

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.

这篇关于对于每个循环不会删除具有特定值的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！