Hide/Unhide Rows Error

Status
Not open for further replies.

r0bism123

Board Regular
Joined
Feb 8, 2018
Messages
57
Hi guys,

I need some help. I have a data entry tab with 184 row chart of accounts. That entry tab is linked to 68 tabs other tabs in the worksheet which are used for different purposes, calculating different items on the chart of the account on each tab, but they all use the same set of rows. So, I have a helper cell on each of the 68 tabs that says "Yes" when a unused row in the chart of accounts has a value of 0.

Then, I have a toggle button on the data entry tab that says with "Hide Rows" and "Unhide Rows". When its pressed, it Hides and Unhides the rows on the chart of accounts that are a value of 0 on all 68 tabs. When I press Unhide, it Unhides all of the rows with the value 0. My issue is that when I enter a value on the data entry tab for a row that is already hidden on the other 68 tabs already, when I press Unhide, the row remains hidden for some reason and I don't know why.

My thought was to remove the if statement criteria of "Yes" and just have all rows on the 68 tabs Unhide regardless of value, yes etc. but when I tried to modify the if statement, I am getting a "runtime error '1004' - unable to set Hidden property of the Range class" error.

Hope someone has some ideas to get this figured out. I'm still learning VB so I am at a loss on how to fix it. Thanks in advance for your help!

The toggle button code is as follows:
Code:
Private Sub ToggleButton9_Click()
    If ToggleButton9.Value Then
        Call Module9.Hide_Rows_Containing_Value_All_Sheets
        ToggleButton9.Caption = "Unhide Rows"
    Else
        ToggleButton9.Caption = "Hide Rows"
        Call Module9.Unhide_All_Rows
    End If
End Sub

Module9 code is as follows:
Code:
Sub Hide_Rows_Containing_Value_All_Sheets()  
 Dim c As Range
 Dim ws As Worksheet
 Dim cR As Range

Set cR = Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
For Each ws In ThisWorkbook.Worksheets
    With ws
        Set cR = .Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
        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("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
For Each ws In ThisWorkbook.Worksheets
    With ws
        Set cR = .Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
        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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Status
Not open for further replies.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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