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

Jason44136

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
 
I think you may have put the code in the wrong place (the unprotect and protect).

Try it like this:

Code:
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.
 
Last edited:
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top