Hide/Unhiding Rows by Value Problem

r0bism123

Board Regular
Joined
Feb 8, 2018
Messages
57
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top