基于值的慢宏隐藏行

Question

我有一个表，我想要完全隐藏或隐藏/显示表中的行，这取决于单元格值是0还是更高。

它在单元格D26中查找0的值；如果0它隐藏行24-51，如果不是0，则隐藏/显示行，具体取决于行34和49之间的C列中是否有值。

下面的宏太慢了，不可能成为可行的选择。有谁能建议一种替代的方法来做到这一点，这可能在几秒钟内，而不是几分钟？我认为这是因为我正在运行For/If/Else循环。

Sub HideManifolds()
'
' HideManifolds Macro
'
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

ChkCol = 3
Manifold1BeginTableRow = 34
Manifold1EndTableRow = 49
Manifold1BeginRow = 24
Manifold1EndRow = 51

    For Manifold1RowCnt = Manifold1BeginRow To Manifold1EndRow
        If Cells(26, 4).Value = 0 Then
            Cells(Manifold1RowCnt, 1).EntireRow.Hidden = True
        Else
            For Manifold1TableRowCnt = Manifold1BeginTableRow To Manifold1EndTableRow
                If Cells(Manifold1TableRowCnt, ChkCol).Value = 0 Then
                    Cells(Manifold1TableRowCnt, ChkCol).EntireRow.Hidden = True
                Else
                    Cells(Manifold1TableRowCnt, ChkCol).EntireRow.Hidden = False
                End If
            Next Manifold1TableRowCnt
        End If
    Next Manifold1RowCnt

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
'
End Sub

Answer 1

我认为Manifold1RowCnt = Manifold1BeginRow To Manifold1EndRow不需要这个循环

代码：

Sub HideManifolds()
'
' HideManifolds Macro
'
Dim hRng As Range, vRng As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

ChkCol = 3
Manifold1BeginTableRow = 34
Manifold1EndTableRow = 49
Manifold1BeginRow = 24
Manifold1EndRow = 51

        If Cells(26, 4).Value = 0 Then
            Rows(Manifold1BeginRow & ":" & Manifold1EndRow).Hidden = True
        Else
            For Manifold1TableRowCnt = Manifold1BeginTableRow To Manifold1EndTableRow
                If Cells(Manifold1TableRowCnt, ChkCol).Value = 0 Then

                  If hRng Is Nothing Then
                   Set hRng = Cells(Manifold1TableRowCnt, ChkCol)
                  Else
                   Set hRng = Union(hRng, Cells(Manifold1TableRowCnt, ChkCol))
                  End If

                Else

                  If vRng Is Nothing Then
                   Set vRng = Cells(Manifold1TableRowCnt, ChkCol)
                  Else
                   Set vRng = Union(vRng, Cells(Manifold1TableRowCnt, ChkCol))
                  End If

                End If
            Next Manifold1TableRowCnt

            If Not hRng Is Nothing Then hRng.EntireRow.Hidden = True
            If Not vRng Is Nothing Then vRng.EntireRow.Hidden = False

        End If



Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
'
End Sub