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

 

[Rick Rothstein]

If your formula outputs either 1 or the empty text string (""), then this macro should work for you...

Sub HideRowsIfOneInColumnA()
  Dim Sh As Worksheet
  For Each Sh In Worksheets
    Sh.Columns("A").SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
  Next
End Sub

 

[Jason44136]

Thank you Rick. Unfortunately, this did not work. I get the following Run-time error '1004': Application-defined or object-defined error

 

[Rick Rothstein]

Thank you Rick. Unfortunately, this did not work. I get the following Run-time error '1004': Application-defined or object-defined error

I think I know what the problem is and it is my fault. See if this code works correctly for you...

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

 

[Jason44136]

Thank you again Rick. This does work. One additional request - Is there a way to add code that will unhide rows or rehide as the user changes the cells that trigger a "1" or "" in column A? This does not seem to work with the current code.
Your time and help is very much appreciated!!

 

[Rick Rothstein]

Thank you again Rick. This does work. One additional request - Is there a way to add code that will unhide rows or rehide as the user changes the cells that trigger a "1" or "" in column A? This does not seem to work with the current code.

I think this code may do what you want...

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

 

[Jason44136]

Perfect. Again, many many thanks!

 

[Jason44136]

I spoke too soon. Unfortunately, when changes are made to the source data on the "Monthly" tab. The other tabs are not updating. I can only get it to work when I click on the run sub play button in the VBA code screen.

I wouldn't be opposed to making this a macro with a command button if that is easier to command all the sheets to update upon click of the button. Any other insight or guidance to make either option work?

 

[Rick Rothstein]

I wouldn't be opposed to making this a macro with a command button if that is easier to command all the sheets to update upon click of the button. Any other insight or guidance to make either option work?

First off, you can run any macro from any worksheet by pressing ALT+F8 (select the macro name from the list and click the Run button.

If you want to run the macro from a CommandButton, put one on the worksheet of your choice and assign my macro to it. If you need help with this part, tell us what kind of CommandButton you used (Forms or ActiveX).

 

[Jason44136]

Thank you again. I did create a FORM commandButton (I believe). I used your Macro and it worked great until I protected all sheets with the password Hskp19
In order to be able to click the button on the protected sheet, i added code I found in these forums to unprotect and reprotect the sheet. When doing this, I get an error that the password is wrong, but it is not wrong (checked case sensitivity, etc).
Here is my current code:
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password = Hskp19
Dim Sh As Worksheet
On Error Resume Next
For Each Sh In Worksheets
Sh.Rows.Hidden = False
Sh.Columns("A").SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
Next
On Error GoTo 0
ActiveSheet.Protect Password = Hskp19
End Sub