遍历65万行的VBA脚本运行后Excel无响应问题求助

看起来你在处理65万行数据时遇到了性能瓶颈，原代码的核心问题在于逐行使用Union合并要删除的范围——当要删除的行数量很大时，这个操作会产生巨大的内存开销，加上循环中不必要的Debug.Print和未提前关闭的Excel耗时功能，直接导致程序卡死。

我给你两个优化方案，其中第二个用AutoFilter的方法几乎能瞬间处理几十万行数据，推荐优先使用：

方案一：优化循环逻辑，减少内存开销 #

这个方案保留了循环思路，但优化了范围合并方式，同时关闭了所有拖慢速度的Excel功能：

Option Explicit
Option Base 1

Sub removeWrongYear_optimized()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim vData As Variant
    Dim rowsToDelete As Range
    
    ' 替换成你实际的工作表名称，避免依赖ActiveSheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 关闭Excel的耗时功能，这是提升速度的关键
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' 确保出错时能恢复Excel设置
    On Error GoTo Cleanup
    
    lastRow = 635475 ' 或者用 ws.Cells(ws.Rows.Count, 20).End(xlUp).Row 自动获取最后一行
    vData = ws.Range(ws.Cells(1, 20), ws.Cells(lastRow, 20)).Value
    
    For i = lastRow To 2 Step -1
        Dim cellValue As String
        cellValue = Trim(vData(i, 1))
        
        ' 这里根据你的实际数据格式调整年份判断逻辑
        ' 情况1：单元格是完整年份（比如2019）
        If IsNumeric(cellValue) Then
            If CLng(cellValue) > 2018 Then
                If rowsToDelete Is Nothing Then
                    Set rowsToDelete = ws.Rows(i)
                Else
                    Set rowsToDelete = Union(rowsToDelete, ws.Rows(i))
                End If
            End If
        ' 情况2：单元格末尾两位是年份（比如"INV2019"或"AB19"）
        ElseIf Len(cellValue) >= 2 Then
            Dim yearSuffix As String
            yearSuffix = Right(cellValue, 2)
            If IsNumeric(yearSuffix) Then
                If 2000 + CLng(yearSuffix) > 2018 Then
                    If rowsToDelete Is Nothing Then
                        Set rowsToDelete = ws.Rows(i)
                    Else
                        Set rowsToDelete = Union(rowsToDelete, ws.Rows(i))
                    End If
                End If
            End If
        End If
    Next i
    
    ' 批量删除符合条件的行
    If Not rowsToDelete Is Nothing Then
        rowsToDelete.Delete
    End If

Cleanup:
    ' 恢复Excel的正常设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    ' 处理可能的错误
    If Err.Number <> 0 Then
        MsgBox "执行出错：" & Err.Description, vbExclamation
    End If
End Sub

方案二：使用AutoFilter（推荐，速度极快） #

Excel的内置筛选功能是专门为大数据优化的，比VBA循环快几个数量级，适合处理几十万行的数据：

Option Explicit

Sub removeWrongYear_Filter()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filterCol As Integer
    
    ' 替换成你实际的工作表名称
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    filterCol = 20 ' 要判断的列（第20列）
    
    ' 关闭耗时功能
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    On Error GoTo Cleanup
    
    ' 获取最后一行数据（自动适配，不用硬编码）
    lastRow = ws.Cells(ws.Rows.Count, filterCol).End(xlUp).Row
    
    ' 清除现有筛选（如果有的话）
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' 设置筛选条件：年份大于2018
    ' 请根据你的实际数据格式调整条件：
    ' - 如果是完整年份数值，用 ">2018"
    ' - 如果是文本格式的完整年份，用 ">""2018"""
    ' - 如果是末尾两位年份，可能需要先添加辅助列转换为完整年份再筛选
    ws.Range(ws.Cells(1, filterCol), ws.Cells(lastRow, filterCol)).AutoFilter _
        Field:=1, Criteria1:=">2018", Operator:=xlAnd
    
    ' 删除筛选出的可见行（跳过表头）
    On Error Resume Next ' 防止没有符合条件的行时出错
    ws.Range(ws.Cells(2, filterCol), ws.Cells(lastRow, filterCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo Cleanup
    
    ' 清除筛选
    ws.AutoFilterMode = False

Cleanup:
    ' 恢复Excel设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    If Err.Number <> 0 Then
        MsgBox "执行出错：" & Err.Description, vbExclamation
        ' 确保筛选被清除
        If ws.AutoFilterMode Then ws.AutoFilterMode = False
    End If
End Sub

关键优化点说明 #

1. 关闭Excel耗时功能：ScreenUpdating、EnableEvents、Calculation这三个设置能避免Excel在循环过程中频繁刷新界面、触发事件和重新计算，直接提升数倍速度。
2. 避免逐行Union：虽然方案一仍用了Union，但相比原代码，我们提前关闭了所有干扰项，且逻辑更严谨；而方案二的筛选完全跳过了循环，是处理大数据的最优解。
3. 严谨的年份判断：原代码的Right(vData(i,1),2)逻辑有风险（比如数据有空格、非数字后缀），优化后的代码增加了格式判断，避免误删。
4. 避免ActiveSheet：硬编码工作表名称能防止误操作其他工作表。

内容的提问来源于stack exchange，提问作者user9730643