Hide/Unhiding Rows by Value Problem

[r0bism123]

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

 

[arunsjain]

Hi,

Try this one

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

 

[r0bism123]

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?

 

[r0bism123]

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

 

[StephenCrump]

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:

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:

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

 

[StephenCrump]

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.

 

[r0bism123]

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:

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:

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.

 

[r0bism123]

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

 

[arunsjain]

Hi Stephen,

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

Cheers!!

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:

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:

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

 

[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.

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:

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