Excel VBA上下文隐藏行宏突然运行缓慢，寻求优化方案

问题背景 #

我有一个Excel工作簿：

• 包含多个工作表
  • 其中5个工作表通过VBA宏根据单元格内容隐藏行（例如当B列对应单元格值为"Hide"时，宏会隐藏整行）

但近期这些隐藏行的宏运行异常缓慢，比如一个不足200行的工作表，运行宏耗时达15秒。

已排查情况 #

• 每个工作表都有独立的Sub，通过按钮点击调用
• 已尝试单独运行单个工作表的宏（未使用Call调用其他宏）
• 代码未使用Select或Activate语句，逻辑简洁
• 已尝试的优化措施：
  • 移除工作表保护（已知保护会拖慢宏运行）
  • 用设置RowHeight=0替代隐藏行，无效果

示例代码（其中一个工作表的Sub） #

Sub HideRowsEmissionsCalc_1()
Application.ScreenUpdating = False

' MsgBox ("Working on Scope 1 & 2")
Dim ws1 As Worksheet

    Set ws1 = Worksheets("Scope I & II Emiss")

'    With ws1
'        .Unprotect Password:="MOS"
'    End With
'
For Each cell In ws1.Range("B3:B178")
If cell.Value = "Hide" Then cell.EntireRow.Hidden = True
Next cell

'    With ws1
'        .Protect Password:="MOS"
'    End With
'
Application.ScreenUpdating = True

End Sub

优化方案 #

1. 批量操作行，避免逐单元格循环 #

逐单元格循环是导致速度慢的核心原因，建议一次性收集所有需要隐藏的行，批量设置隐藏：

Sub HideRowsEmissionsCalc_1()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual ' 暂停自动计算
    Application.EnableEvents = False ' 禁用事件触发
    
    Dim ws1 As Worksheet
    Dim targetRows As Range
    Dim cell As Range
    
    Set ws1 = Worksheets("Scope I & II Emiss")
    
    ' 先取消所有行隐藏，避免重复操作
    ws1.Rows.Hidden = False
    
    ' 遍历收集需要隐藏的行
    For Each cell In ws1.Range("B3:B178")
        If cell.Value = "Hide" Then
            If targetRows Is Nothing Then
                Set targetRows = cell.EntireRow
            Else
                Set targetRows = Union(targetRows, cell.EntireRow)
            End If
        End If
    Next cell
    
    ' 批量隐藏行
    If Not targetRows Is Nothing Then targetRows.Hidden = True
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

2. 使用AutoFilter实现快速隐藏 #

如果数据结构规整，用筛选功能实现行隐藏的效率远高于循环：

Sub HideRowsWithFilter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Scope I & II Emiss")
    
    ' 清除原有筛选
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    
    ' 筛选显示非"Hide"的行，等效于隐藏值为"Hide"的行
    ws1.Range("B2:B178").AutoFilter Field:=1, Criteria1:="<>" & "Hide"
    
    ' 恢复默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

3. 排查工作表额外开销 #

如果上述优化后还是慢，检查工作表是否存在以下情况：

• 大量条件格式：操作行时会触发格式重绘，可暂时移除条件格式测试
• 数组公式/volatile函数：比如NOW()、OFFSET()这类函数会频繁触发重算，尽量替换为非volatile函数
• 隐藏对象/图片：工作表中过多隐藏的形状、图片也会拖慢行操作

内容的提问来源于stack exchange，提问作者Matt O'Sullivan