删除无法用 SpecialCells 抓取的行的最快方法 [英] Fastest way to delete rows which cannot be grabbed with SpecialCells

问题描述

基于关于另一个问题这个网站我开始想知道在特定条件下删除所有行的最快方法.

Based on another question on this site I started wondering about the fastest way to delete all rows with a certain condition.

上面提到的问题有多种解决方案:

The above-referenced question came with various solutions:

(1) 遍历sheet上的所有行(向后)，将满足条件的行一一删除.

(1) Loop through all rows on the sheet (backward) and delete all rows one-by-one which meet the condition.

(2) 先将适用范围移动到数组中，然后评估数组中的条件，并在此基础上一一删除底层工作表上的所有行.

(2) Move the applicable range into an array first and then evaluate the conditions in the array and -- based on that -- delete all rows one-by-one on the underlying sheet.

一个可能的改进可能是删除块中的所有行以减少访问工作表的开销.但是如果你走这条路线，那么你有多种选择来存储"范围，然后再实际删除它们:

A possible improvement might be to delete all rows in chunks to reduce the overhead of accessing to the worksheet. But if you go this route then you have various options to "store" the ranges before you actually delete them:

(1) 使用Intersect合并需要删除的范围.

(1) Use Intersect to merge the ranges which should be deleted.

(2) 简单地写一个String，其中包含所有要删除的行.

(2) Simply write a String with all the rows to be deleted.

那么，最快的方法是什么?

So, which is the fastest way to do that?

推荐答案

一种有效的解决方案是通过对标签进行排序来标记所有行以保留并移动所有行以在末尾删除.这样，复杂度不会随着要删除的行数而增加.

One efficient solution is to tag all the rows to keep and move all the rows to delete at the end by sorting the tags. This way, the complexity doesn't increase with the number of rows to delete.

此示例在不到一秒的时间内删除了 50000 行，其中 I 列等于 2 的所有行:

This example deletes in less than a second, for 50000 rows, all the rows where column I is equal to 2:

Sub DeleteMatchingRows()
    Dim rgTable As Range, rgTags As Range, data(), tags(), count&, r&

    ' load the data in an array
    Set rgTable = ActiveSheet.UsedRange
    data = rgTable.Value

    ' tag all the rows to keep with the row number. Leave empty otherwise.
    ReDim tags(1 To UBound(data), 1 To 1)
    tags(1, 1) = 1  ' keep the header
    For r = 2 To UBound(data)
      If data(r, 9) <> 2 Then tags(r, 1) = r  ' if column I <> 2 keep the row
    Next

    ' insert the tags in the last column on the right
    Set rgTags = rgTable.Columns(rgTable.Columns.count + 1)
    rgTags.Value = tags

    ' sort the rows on the tags which will move the rows to delete at the end
    Union(rgTable, rgTags).Sort key1:=rgTags, Orientation:=xlTopToBottom, Header:=xlYes
    count = rgTags.End(xlDown).Row

    ' delete the tags on the right and the rows that weren't tagged
    rgTags.EntireColumn.Delete
    rgTable.Resize(UBound(data) - count + 1).Offset(count).EntireRow.Delete
End Sub

请注意，它不会改变行的顺序.

Note that it doesn't alter the order of the rows.

这篇关于删除无法用 SpecialCells 抓取的行的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！