vba - 隐藏行宏需要很长时间才能运行

Question

有没有办法让这段代码运行得更快？我正在尝试隐藏多个工作表中空白的行。

Option Explicit

Private Sub HideRows_Click()

Dim ws As Worksheet, c As Range

    Application.ScreenUpdating = False
    On Error Resume Next

    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "Sheet1", "Sheet2", "Sheet3"
        'sheets to exclude
            'do nothing

        Case Else 'hide rows on these sheets
            For Each c In ws.Range("AJ16:AJ153,AJ157:AJ292")
                c.EntireRow.Hidden = c.Value = 0
            Next c
        End Select
    Next ws

    Application.ScreenUpdating = True

End Sub

Answer 1

以下是对您的代码进行的一些更改，旨在加快速度:

关闭计算、事件和状态栏

首先对 AJ 中的所有值进行分组通过 Union() 没有值函数，然后调用 EntireRow.Hide在该组合范围内

老实说，这是一个非常干净的代码!

Option Explicit

Private Sub HideRows_Click()

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

    'On Error Resume Next

    Dim ws As Worksheet
    For Each ws In Worksheets

        Select Case ws.name
            Case "Sheet1", "Sheet2", "Sheet3" 'sheets to exclude
                'do nothing

            Case Else 'hide rows on these sheets
                Dim unioned As Range
                Set unioned = Nothing

                Dim c As Range
                For Each c In ws.Range("AJ16:AJ153,AJ157:AJ292")
                    If Len(c.Value2) = 0 Then
                        If unioned Is Nothing Then
                            Set unioned = c
                        Else
                            Set unioned = Union(unioned, c)
                        End If
                    End If
                Next c

                unioned.EntireRow.Hidden = True
        End Select

    Next ws

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With

End Sub