CODEWITHSUNDEEP
excelvba
Vinz B
Vinz B

Reputation: 35

Hide and Unhide Toggle button - running very slow

I have a workbook with 12 sheets and I have placed one Command button in each sheet to hide/unhide rows. For hiding specific rows, I have typed "A" in column A, for every row that needs to be hidden. So code works but it runs forever, takes long time and very slow in hiding or unhiding the rows. In some sheets total number of rows to check are 100 and some sheets it is 750. Please advice the reason for slow running or Is there a better way I can make it work faster. Here is the code:-

Private Sub CommandButton1_Click()
Sheet2.Unprotect ("aaa")
Dim rng As Range
Dim iRow As Range
Dim hidden_status As Boolean

CommandButton1.Caption = "Show / Hide Guidelines"

On Error Resume Next

   Set rng = Range("A1:A750")
    For Each iRow In rng.Rows
        If iRow.Text = "A" Then
           With iRow.EntireRow
                 hidden_status = .Hidden
                .Hidden = Not hidden_status
           End With
        End If
    Next iRow

On Error GoTo 0
Sheet2.Protect ("aaa")
End Sub

Upvotes: 0

Views: 394

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

You could use AutoFilter.

This procedure will hide any row that doesn't contain a value in column A on whichever sheet reference is passed to it. If the filter is already applied it will remove it.

Public Sub HideA(wrkSht As Worksheet)

    With wrkSht
        If .FilterMode Then
            .ShowAllData
        Else
            .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)) _
                .AutoFilter Field:=1, Criteria1:="=", VisibleDropDown:=False
        End If
    End With

End Sub

On each sheet that contains a button add this code for the button (renaming the procedure as required): 

Private Sub CommandButton1_Click()
    HideA ActiveSheet
End Sub

The one downside to this is that it doesn't include cell A1 in the filtering.

Upvotes: 1

PeterT
PeterT

Reputation: 8557

Each time you hide a row, Excel then is stopping to update your screen (and potentially perform calculations). So to help really speed things up, disable all the screen updates and application events (including calculations) until you've finished hiding.

So with a Sub like this:

Private Sub SetUpdates(ByVal newState As Boolean)
    With Application
        .ScreenUpdating = newState
        .EnableEvents = newState
        .DisplayAlerts = newState
    End With
End Sub

You can do something like this:

Private Sub CommandButton1_Click()
    SetUpdates newState:=False

    '--- hide your rows here

    SetUpdates newState:=True
End Sub

Upvotes: 3

Related Questions