如何加速处理4-5千行数据的行隐藏VBA代码？

原代码的性能瓶颈 #

原代码处理4000-5000行耗时5分钟，核心问题在于：

• 双重嵌套循环逐单元格读写：Excel单元格IO是VBA里最慢的操作之一，5000行×5000行就是2500万次IO，耗时爆炸
• 每次外层循环都重复获取Sheet1的最后行数，做了完全没必要的冗余计算
• 逐行设置Hidden属性，即使关了屏幕更新，多次操作行对象也会产生额外开销

优化后的代码 #

Sub FastFilterNameDuplicate()
    ' 关闭Excel的全局耗时属性
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim wsDefault As Worksheet, wsSheet1 As Worksheet
    Dim arrDefault As Variant, arrSheet1 As Variant
    Dim matchColl As Collection
    Dim lastRowDefault As Long, lastRowSheet1 As Long
    Dim i As Long, hideRange As Range
    
    ' 定义工作表对象，避免重复写Worksheets("xxx")
    Set wsDefault = ThisWorkbook.Worksheets("Default")
    Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")
    Set matchColl = New Collection
    
    ' 一次性读取整列数据到数组（内存操作，速度极快）
    lastRowDefault = wsDefault.Cells(wsDefault.Rows.Count, "G").End(xlUp).Row
    arrDefault = wsDefault.Range("G1:G" & lastRowDefault).Value
    
    lastRowSheet1 = wsSheet1.Cells(wsSheet1.Rows.Count, "A").End(xlUp).Row
    arrSheet1 = wsSheet1.Range("A1:A" & lastRowSheet1).Value
    
    ' 将Sheet1的匹配项存入集合，利用集合的快速查找特性
    On Error Resume Next ' 忽略重复项报错
    For i = 1 To lastRowSheet1
        matchColl.Add arrSheet1(i, 1), Key:=CStr(arrSheet1(i, 1))
    Next i
    On Error GoTo 0 ' 恢复错误处理
    
    ' 遍历Default的数组，收集需要隐藏的行
    For i = 1 To lastRowDefault
        ' 集合查找是O(1)，替代原来的内层循环
        On Error Resume Next
        matchColl.Item(CStr(arrDefault(i, 1)))
        If Err.Number = 0 Then ' 找到匹配项
            If hideRange Is Nothing Then
                Set hideRange = wsDefault.Rows(i)
            Else
                Set hideRange = Union(hideRange, wsDefault.Rows(i))
            End If
        End If
        On Error GoTo 0
    Next i
    
    ' 批量设置隐藏，只操作一次
    If Not hideRange Is Nothing Then
        hideRange.EntireRow.Hidden = True
    End If
    
    ' 恢复Excel全局属性
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "Done"
End Sub

关键优化点解析 #

• 数组读写替代单元格操作：把G列和A列的数据一次性读入内存数组，所有比对都在内存里完成，比逐单元格操作快几十倍
• 集合快速查找：用集合存储Sheet1的列表，查找匹配项的时间从O(m)降到O(1)，彻底消除嵌套循环的性能灾难
• 批量设置行隐藏：先把所有要隐藏的行合并成一个Range对象，最后一次性设置隐藏，减少Excel的内部操作次数
• 关闭更多全局属性：EnableEvents避免触发不必要的工作表事件，xlCalculationManual暂停自动计算，进一步减少后台开销
• 移除冗余代码：删掉原代码里没用到的变量，用工作表对象替代重复的Worksheets("xxx")，代码更简洁也更快

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