VBA 脚本导致 Excel 在 15 次循环后没有响应

Question

我正在运行一个脚本来查找和删除包含 2018 年之后数据的行。我正在搜索大约 650000 行。每次我在 5 秒后运行脚本时，我的光标都会变成旋转的圆圈，并且 excel 程序变得无响应。这是我正在使用的代码。

Option Explicit
Option Base 1 'row and column index will match array index

Sub removeWrongYear()

Dim i As Long, yearA As Long, rowsCnt As Long
Dim rowsToDelete As Range
Dim vData As Variant

With ActiveSheet

    '1st to 635475 row, 20th column
    vData = Range(.Cells(1, 20), .Cells(635475, 20))

    For i = UBound(vData) To 2 Step -1
       If Val(Right(vData(i,1),2)) > 17 Then
        Debug.Print Val(Right(vData(i,1),2))
            rowsCnt = rowsCnt + 1

            If rowsCnt > 1 Then
                Set rowsToDelete = Union(rowsToDelete, .Rows(i))
            ElseIf rowsCnt = 1 Then
                Set rowsToDelete = .Rows(i)
            End If

        End If
    Next i

End With

If rowsCnt > 0 Then
    Application.ScreenUpdating = False
    rowsToDelete.EntireRow.Delete
    Application.ScreenUpdating = True
End If

End Sub

Answer 1

Every time I run the script after 5 seconds my cursor becomes the spinning circle and the excel program becomes unresponsive.

这很正常。 VBA 在单个可用的 UI 线程上运行，与 Excel 运行的线程相同。当它忙于运行您的循环时，它无法响应其他刺激，并通过在标题栏中放置“(未响应)”来告诉您，直到它完成工作并能够恢复做它需要做的所有其他事情(即监听鼠标和键盘消息等)。

您可以在该循环的主体中添加一点 DoEvents 以允许 Excel 在迭代之间呼吸和处理未决消息，但是有一个问题:首先，您的代码将花费更长的时间才能完成，其次，如果用户能够在该循环的中间选择/激活另一个工作表，那么这个不合格的 Range 调用:

vData = Range(.Cells(1, 20), .Cells(635475, 20))

...将是运行时错误 1004 的来源，因为您不能执行 Sheet1.Range(Sheet2.Cells(1,20), Sheet2.Cells(635475,20)) 并期望 Excel 知道如何处理它(假设 Sheet2 在循环开始时处于事件状态，并且用户在循环中间激活了 Sheet1)。

This answer当涉及很多 行时，提供似乎最有效的有条件地删除行的方法。如果可以，添加一个辅助列来计算您的条件(例如，让它返回 TRUE 以保留行，返回 FALSE 以删除行)，然后使用 Worksheet .Replace 和 Worksheet.SpecialCells 执行过滤和删除:

.Columns("Z:Z").Replace What:=False, _
                        Replacement:="", _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False, _
                        SearchFormat:=False, _
                        ReplaceFormat:=False
.Columns("Z:Z").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

那么您就不需要循环了，它实际上可能在您数到 5 秒之前就完成了。

除此之外，长时间运行的操作就是:长时间运行的操作。拥有它:

Application.StatusBar = "Please wait..."
Application.Cursor = xlWait
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

'..code..

Application.Cursor = xlDefault
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.StatusBar = False