Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Hide/Unhiding Rows by Value Problem

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Hide/Unhiding Rows by Value Problem

    Hello All,

    Thanks for your help in advance. I created this macro below which hides/unhides the entire row if value is zero. Everything works fine until the spreadsheet is asked to hide too many rows and then crashes. Is there a work-around here? I am at a loss...


    Sub Hide_Rows_Containing_Value_All_Sheets()

    Dim c As Range
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    For Each c In ws.Range("DD8:DD12,DD19:DD28,DD35:DD209").Cells
    If c.Value = "Yes" Then
    c.EntireRow.Hidden = True
    End If
    Next c
    Next ws

    End Sub

    Sub Unhide_All_Rows()

    For Each ws In ActiveWorkbook.Worksheets
    For Each c In ws.Range("DD8:DD12,DD19:DD28,DD35:DD209").Cells
    If c.Value = "Yes" Then
    c.EntireRow.Hidden = False
    End If
    Next c
    Next ws

    End Sub

  2. #2
    Board Regular
    Join Date
    Apr 2016
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Hi,

    Try this one

    Code:
    Sub Hide_Rows_Containing_Value_All_Sheets()
     Dim c As Range
     Dim ws As Worksheet
     Dim cR As Range
     
     Set cR = Range("DD8:DD12,DD19:DD28,DD35:DD209")
     
     Application.ScreenUpdating = False
     
     For Each ws In ThisWorkbook.Worksheets
     
        For Each c In cR
            If c.Value = "Yes" Then
            c.EntireRow.Hidden = True
            End If
        Next c
     
     Next ws
    Application.ScreenUpdating = True
     End Sub
     
    
    
    
    Sub Unhide_All_Rows()
     Dim c As Range
     Dim ws As Worksheet
     Dim cR As Range
     
     Set cR = Range("DD8:DD12,DD19:DD28,DD35:DD209")
     
     Application.ScreenUpdating = False
     
     For Each ws In ThisWorkbook.Worksheets
     
        For Each c In cR
            If c.Value = "Yes" Then
            c.EntireRow.Hidden = False
            End If
        Next c
     
     Next ws
    Application.ScreenUpdating = True
     End Sub


    Cheers!!
    Last edited by arunsjain; Feb 18th, 2018 at 08:42 PM.

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Hi Arunsjain,

    Thanks for the try. Unfortunately, I got the same result happened. It's really odd. The code works up to a certain number of rows, but if it is too much, it freezes and wont respond.

    Any other ideas?

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    It might be worth noted my workbook is 75 sheets large.

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Your code should run reasonably quickly, even with 75 worksheets.

    Assuming you're in automatic calculation mode, you can generate time savings by not triggering a calculation each time you hide/unhide a row:

    Code:
    Application.Calculation = xlCalculationManual
    
    'Your code
    
    Application.Calculation = xlCalculationAutomatic
    At the moment you're looping through 190 rows in each worksheet. Are you saying that the problem happens if you expand the number of rows?

    By the way, there is an error in your code - you're looping through each worksheet, but your range reference always points to the active sheet: Try:

    Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209")
            For Each c In cR
                If c.Value = "Yes" Then c.EntireRow.Hidden = True
            Next c
        End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Finally, are rows hidden only when the value in column DD is "Yes"? If so, you don't need to loop through every row when unhiding. Instead, you can just have:

    .Range("DD8:DD12,DD19:DD28,DD35:DD209").EntireRow.Hidden = False
    Last edited by StephenCrump; Feb 18th, 2018 at 09:58 PM.

  6. #6
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Quote Originally Posted by StephenCrump View Post
    By the way, there is an error in your code - you're looping through each worksheet, but your range reference always points to the active sheet:
    Oops sorry! Your original post correctly qualified the range reference with ws.

    The problem was in the second post.

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Quote Originally Posted by StephenCrump View Post
    Your code should run reasonably quickly, even with 75 worksheets.

    Assuming you're in automatic calculation mode, you can generate time savings by not triggering a calculation each time you hide/unhide a row:

    Code:
    Application.Calculation = xlCalculationManual
    
    'Your code
    
    Application.Calculation = xlCalculationAutomatic
    At the moment you're looping through 190 rows in each worksheet. Are you saying that the problem happens if you expand the number of rows?

    By the way, there is an error in your code - you're looping through each worksheet, but your range reference always points to the active sheet: Try:

    Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209")
            For Each c In cR
                If c.Value = "Yes" Then c.EntireRow.Hidden = True
            Next c
        End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Finally, are rows hidden only when the value in column DD is "Yes"? If so, you don't need to loop through every row when unhiding. Instead, you can just have:

    .Range("DD8:DD12,DD19:DD28,DD35:DD209").EntireRow.Hidden = False
    Hi StephenCrump,

    Thanks for looking at this one. I'll give this a try. I'm a noob at VB so it may take awhile.

    No, what I'm saying is when the code loops through the 190 rows, it works fine if the value set the code isn't zero. If it's zero then it triggers to hide the row. It only freezes when I make the zero values in 40+ rows otherwise it works perfectly fine. It's annoying.

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Hi StephenCrump,

    I got your code to work. It takes about 2 minutes to loop through the code but it doesn't crash excel so I'll take it.

    I may have messed up so would you take a last quick look and see if this is good:

    Sub Hide_Rows_Containing_Value_All_Sheets()
    Dim c As Range
    Dim ws As Worksheet
    Dim cR As Range

    Set cR = Range("DD8:DD12,DD19:DD28,DD35:DD209")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each ws In ThisWorkbook.Worksheets
    With ws
    Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209")
    For Each c In cR
    If c.Value = "Yes" Then c.EntireRow.Hidden = True
    Next c
    End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

    Sub Unhide_All_Rows()
    Dim c As Range
    Dim ws As Worksheet
    Dim cR As Range

    Set cR = Range("DD8:DD12,DD19:DD28,DD35:DD209")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each ws In ThisWorkbook.Worksheets
    With ws
    Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209")
    For Each c In cR
    If c.Value = "Yes" Then c.EntireRow.Hidden = False
    Next c
    End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

  9. #9
    Board Regular
    Join Date
    Apr 2016
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Hi Stephen,

    Thanks for correcting code. You put code in right section.

    Cheers!!

    Quote Originally Posted by StephenCrump View Post
    Your code should run reasonably quickly, even with 75 worksheets.

    Assuming you're in automatic calculation mode, you can generate time savings by not triggering a calculation each time you hide/unhide a row:

    Code:
    Application.Calculation = xlCalculationManual
    
    'Your code
    
    Application.Calculation = xlCalculationAutomatic
    At the moment you're looping through 190 rows in each worksheet. Are you saying that the problem happens if you expand the number of rows?

    By the way, there is an error in your code - you're looping through each worksheet, but your range reference always points to the active sheet: Try:

    Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209")
            For Each c In cR
                If c.Value = "Yes" Then c.EntireRow.Hidden = True
            Next c
        End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Finally, are rows hidden only when the value in column DD is "Yes"? If so, you don't need to loop through every row when unhiding. Instead, you can just have:

    .Range("DD8:DD12,DD19:DD28,DD35:DD209").EntireRow.Hidden = False

  10. #10
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/Unhiding Rows by Value Problem

    Quote Originally Posted by r0bism123 View Post
    I got your code to work. It takes about 2 minutes to loop through the code but it doesn't crash excel so I'll take it.
    Great, I'm glad you got it working. I'm guessing that with 75 worksheets, your workbook is probably pretty slow in any event?

    You can delete the first Set CR in each Sub, i.e.

    Set cR = Range("DD8:DD12,DD19:DD28,DD35:DD209")

    (But keep the
    Set cR = .Range("DD8:DD12,DD19:DD28,DD35:DD209") inside the loop).

    And if rows are hidden only when the value in column DD is "Yes", then you can simplify the Unhide to:

    Code:
    Sub Unhide_All_Rows()
        
        Dim ws As Worksheet
            
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        For Each ws In ThisWorkbook.Worksheets
            ws.Range("DD8:DD12,DD19:DD28,DD35:DD209").EntireRow.Hidden = False
        Next ws
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    
    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
  •