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

我正在运行一个脚本来查找并删除包含 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

每次我在 5 秒后运行脚本时，光标都会变成旋转的圆圈，并且 Excel 程序变得无响应。

这很正常。 VBA 运行在单个可用的 UI 线程上，与 Excel 运行的线程相同。当它正忙于运行循环时，它无法respond到其他刺激，并告诉您通过在标题栏中输入“（不响应）”，直到它完成工作并能够恢复执行它需要执行的所有其他操作（即侦听鼠标和键盘消息等） 。

你可以添加一点DoEvents在该循环的主体中，允许 Excel 在迭代之间呼吸并处理待处理消息，但有一个问题：首先，您的代码将需要更长的时间才能完成，其次，如果用户能够选择/激活另一个工作表循环的中间，然后这个不合格Range call:

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

...将成为运行时错误 1004 的根源，因为你不能这样做Sheet1.Range(Sheet2.Cells(1,20), Sheet2.Cells(635475,20))并期望 Excel 知道如何处理（假设Sheet2循环开始时处于活动状态，并且用户激活Sheet1在它的中间）。

这个答案 https://stackoverflow.com/a/49015509/1188513提供了最有效的方法来有条件地删除行a lot涉及行数。如果可以的话，添加一个辅助列来计算您的标准（例如，使其返回TRUE要保留的行和FALSE用于要删除的行），然后使用Worksheet.Replace and 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