excel - VBA 宏导致 Excel 崩溃

Question

各位网友大家好，

我正在运行一个宏来删除包含特定值的整行。该代码在小型数据集上运行良好，但在当前数据集(约 22,000 条记录)上，它始终使 Excel (2010) 崩溃。代码如下。如果没有将数据分割成更小的 block 并一次又一次地运行宏，我不知道该怎么做。

感谢任何帮助，这是代码:

Sub CleanOcc()

'Row counting
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim Lrow2 As Long

With Sheets("Occ_Prep")

    'Cleans the occ_prep sheet ready for upload (Column and value can be changed)
    Sheets("Occ_Prep").Activate

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
    For Lrow2 = Lastrow To Firstrow Step -1

        'We check the values in the A column in this example
        With .Cells(Lrow2, "K")


            If Not IsError(.Value) Then

                If .Value = "0" Then .EntireRow.Delete
                'This will delete each row with the Value "ron"
                'in Column A, case sensitive.

            End If

        End With

    Next Lrow2

End With



End Sub

Answer 1

同意 Siddharth 的评论，自动过滤器是一种可行的方法。这应该会快很多。

Option Explicit
Sub delrows()
    Dim ws As Worksheet
    Dim LR As Long
    Dim rng As Range, frng As Range

    Application.ScreenUpdating = False

    Set ws = Sheets("dataset")  '<-- Change this to name of your worksheet
    With ws
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .AutoFilterMode = False
        Set rng = .Range("A1:C" & LR) '<-- Assuming K is the last column
        rng.AutoFilter 3, "0" '<-- 11 referes to Column K
        Set frng = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible) '<-- Don't delete the header
        frng.EntireRow.Delete
        .AutoFilterMode = False
    End With

    Application.ScreenUpdating = True
End Sub

编辑:我刚刚在大约 5 秒内清理了大约 20000 行(3 列)数据。显然，这也取决于有多少场比赛。