Slow macro hiding rows based on value

Question

I have a table that I want to completely hide or hide/show rows within the table, depending on whether a cell value is 0 or above.

It looks for a value of 0 within cell D26; if 0 it hides rows 24-51, if not 0 it hides/shows rows depending on whether there is a value in the C column between rows 34 and 49.

The macro below is too slow to be a viable option. Can anyone suggest an alternative way of doing this, that might work in a few seconds rather than a few minutes? I think it's because I'm running the For/If/Else loop.

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

See [Set Auto Filtering multiple wildcards](http://stackoverflow.com/questions/16602872/set-auto-filtering-multiple-wildcards) for a method of setting the [AutoFilter](https://msdn.microsoft.com/en-us/library/office/aa221844.aspx) criteria with a dictionary's keys (or items). — , Jan 23 '16 at 19:36
I wonder how this code with this small data takes few minutes, is there a lot of conditional formatting? — Fadi, Jan 23 '16 at 19:53
@Fadi forgot to say that it's technically doing this macro 6 times.. for the 6 tables that I have on the same sheet. I just posted it for one because they're all just duplicates of each other (apart from row/cell numbers changing!) — , Jan 23 '16 at 19:59
I think you don't need this loop `For Manifold1RowCnt = Manifold1BeginRow To Manifold1EndRow`. — Fadi, Jan 23 '16 at 20:14
Having edited that loop out, I then realised the Else loop is pretty much the same as the one you helped out with yesterday! Got it working now! Thanks again, @Fadi — , Jan 23 '16 at 20:31
@iAGPx, please test my code and ensure that it give you correct result. because i lost with long variables names. — Fadi, Jan 23 '16 at 20:43
@iAGPx, I think using `Auto Filter` as Jeeped comment will be a lot faster. — Fadi, Jan 23 '16 at 21:03
That is good, I was not sure that it will work as you want . — Fadi, Jan 23 '16 at 21:10

Fadi · Accepted Answer · 2016-01-23T20:47:39.543

I think you don't need this loop For Manifold1RowCnt = Manifold1BeginRow To Manifold1EndRow

code:

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

Slow macro hiding rows based on value

1 Answers1