删除两个条件的行的最有效方法 [英] Most Efficient Method for Removing Rows with Two Conditions

问题描述

我要执行的操作是删除电子表格中两个单独列中值为0的行，因为这表明该行中没有有用的数据.虽然可以将 For循环与 If/Then 条件一起使用，但我发现这令人难以置信，因为我的数据集有〜51,000行，这实际上需要几个小时，如果不是几天，就可以运行.

What I'm trying to do is to remove rows in my spreadsheet that have values of 0 in two separate columns, as this indicates that there is no useful data in that row. While it is possible to use a For loop with If/Then conditions, I've found that this is incredibility slow because my dataset has ~51,000 rows andit will literally take hours, if not days, to run.

经过一番搜索，我发现了另一种方法，我认为它是一种更快的方法，目前正在使用:

After some searching, I came across an alternate method, that I believe to be faster and am currently using:

Public Sub deleteRows()
     ' source: http://www.ozgrid.com/forum/showthread.php?t=64364
    Dim lastRow As Long
    Dim n As Long
    Dim RT As Date
    RT = Time

    With ActiveSheet
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With

    For n = lastRow To 2 Step -1
        If (Trim(ActiveSheet.Cells(n, 3).Value) = 0) And (Trim(ActiveSheet.Cells(n, 6).Value) = 0) Then
            ActiveSheet.Cells(n, 1).EntireRow.Delete
        End If
    Next n
    MsgBox Format(Time - RT, "hh:mm:ss")
End Sub

但是，我仅用1000个单元进行了一次测试，并且花费了402秒的时间才能运行，这表明以相同的速度运行所有数据将花费5.584个小时.

However, I ran a test with only 1000 cells, and it took 402 seconds to run, which indicates it would take 5.584 hours to run all of the data at the same rate.

有什么方法可以加快这一过程吗?我缺少明显的东西吗?

Is there any way to speed up the process? Am I missing something obvious?

*此代码作为另一个代码的子部分运行，其中在代码的开头声明了ScreenUpdating = False.

* This code is running as a subpart of another code, where ScreenUpdating = False is declared in the beginning of the code.

推荐答案

以下是使用自动过滤器的方法:

Here's an approach using autofilter:

Public Sub deleteRows()
    Dim lastRow As Long
    Dim rng As Range
    Dim start

    start = Timer
    With ActiveSheet
        .AutoFilterMode = False
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        '~~> Set the range of interest, no need to include the entire data range
        Set rng = .Range("B2:F" & lastRow)
        rng.AutoFilter 2, 0 '~~> filter zeros on C column
        rng.AutoFilter 5, 0 '~~> filter zeros on F column
        '~~> Delete the entire row of the remaining visible cells
        rng.Offset(1, 0).Resize(rng.Rows.Count - 1) _
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
    Debug.Print (Timer - start) * 1000 '~~> in ms
End Sub

对示例数据进行了测试(单元格B2:L52802).
在我的计算机上，删除操作耗时11699.22毫秒或大约11.7秒.
我认为，与您预计的5个小时相比，该速度已经足够快(具体速度要快1700倍).

Tried and tested on sample data (Cell B2:L52802).
The deleting took 11699.22 ms or roughly 11.7 seconds in my machine.
I think that's fast enough compared to your estimated 5 hours (1700 times faster to be specific).

这篇关于删除两个条件的行的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！