VBA code to hide/unhide rows that have a "1" in column A - 32 sheets
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,958
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,958
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Jason44136 View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!!

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,958
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Jason44136 View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Perfect. Again, many many thanks!

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,958
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Jason44136 View Post
    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).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •