VBA code to hide/unhide rows that have a "1" in column A - 32 sheets

[Jason44136]

Greetings - I have learned a lot over the years from reviewing these threads. I'm hoping someone can assist. I have a workbook with 33 sheets. Two summary sheets and 31 data sheets. The first sheet "Monthly" has data within that will be changed regularly. All other sheets have an A column that has formulas built to show a "1" if criteria determines the row should be hidden. The "Monthly" tab also has an A column that is blank and will NOT be impacted by the VBA (as it shouldnt) because there is no "1" in column A anywhere.

I found the below code which does work, but it takes multiple minutes to run. Does anyone have a different suggestion on code to quickly hide all rows with a "1" in column A for all sheets?

(There are only 60 rows per sheet)

Sub doStuff()
Dim i As Worksheet, myLoop As Long
For Each i In Worksheets
With i
For myLoop = 1 To 100
If .Cells(myLoop, "A").Value = 1 Then
.Rows(myLoop).Hidden = True
Else
.Rows(myLoop).Hidden = False
End If
Next myLoop
End With
Next i
End Sub

 

[Zanmato]

I think you may have put the code in the wrong place (the unprotect and protect).

Try it like this:

Private Sub CommandButton1_Click()

Dim Sh As Worksheet
  On Error Resume Next
  For Each Sh In Worksheets
Sh.Unprotect "Hskp19"
    Sh.Rows.Hidden = False
    Sh.Columns("A").SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
Sh.Protect "Hskp19"
  Next
  On Error GoTo 0
End Sub

I don't know if you need to unprotect the sheet to click the button? If so this won't work sorry.

EDIT: D'oh just re-read your post where you clearly state you do need to unprotect it to click the button.
And now I'm not even sure if you need to unprotect each sheet to hide the rows... probably not... Yeah just ignore this post.

EDIT2: I've never needed to put "Password =" when using protect commands but I have always used quote marks around the password, maybe it will work if you use your code from your last post but change those two lines to:

ActiveSheet.Unprotect "Hskp19"

and

ActiveSheet.Protect "Hskp19"

EDIT3: Hopefully the last one. Just noticed something and feel stupid. If you need to unprotect to click the button, how does putting unprotect in the button_click code work? I am totally confused now.

 

[Jason44136]

Thank you yet again. Sorry to confuse you. I had tried a few things before sending the post then confused myself. I used the recent revision and it DOES work as necessary. I think we are good to move forward. Your help is greatly appreciated! I have tested a few different ways.