CODEWITHSUNDEEP
vbaexcel
user5823288
user5823288

Reputation:

Slow macro hiding rows based on value

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

Upvotes: 2

Views: 182

Answers (1)

Fadi
Fadi

Reputation: 3322

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

Upvotes: 0

Related Questions