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
365
Platform
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,023
Office Version
2010
Platform
Windows
If your formula outputs either 1 or the empty text string (""), then this macro should work for you...
Code:
Sub HideRowsIfOneInColumnA()
  Dim Sh As Worksheet
  For Each Sh In Worksheets
    Sh.Columns("A").SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
  Next
End Sub
 

Jason44136

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
365
Platform
Windows
Thank you Rick. Unfortunately, this did not work. I get the following Run-time error '1004': Application-defined or object-defined error
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,023
Office Version
2010
Platform
Windows
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...
Code:
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

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,023
Office Version
2010
Platform
Windows
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...
Code:
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

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
365
Platform
Windows
Perfect. Again, many many thanks!
 

Jason44136

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,023
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,837
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top