Unhide Row Script

[r0bism123]

Hi gang,

I need some help. I want to unhide the below range without having the "Yes" criteria. So, when I press my Toggle Button, the action unhides all rows without criteria for all of the sheets in the workbook. I have been playing with the code and I can't figure it out. Your help is appreciated!

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

 

[silentwolf]

Hi,
when you assigend a toggel button to your worksheet and you like to drigger the code when you click the toggle button then you need to use the toggel button events.
You can either write a code that hides the rows in a seperate code and then assign the togglebutton event to drigger the code.
Or you enter the code into the toggelbutton event itself.

 

[My Aswer Is This]

Well this script is trying to unhide a bunch of rows on all the sheets in your workbook.

And using a Toggle button is not the way to run a script like this.

Are you now saying you want to unhide all rows on one sheet or on all sheets.

And how do you plan to hide the rows afterwards?

 

[r0bism123]

Hi,
when you assigend a toggel button to your worksheet and you like to drigger the code when you click the toggle button then you need to use the toggel button events.
You can either write a code that hides the rows in a seperate code and then assign the togglebutton event to drigger the code.
Or you enter the code into the toggelbutton event itself.

Yes, the event is in a module which assigned it to the toggle button.

I got the code below to work on a single sheet but not the whole workbook. I am still a noob at VB...

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

    Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261").EntireRow.Hidden = False

Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[r0bism123]

Well this script is trying to unhide a bunch of rows on all the sheets in your workbook.

And using a Toggle button is not the way to run a script like this.

Are you now saying you want to unhide all rows on one sheet or on all sheets.

And how do you plan to hide the rows afterwards?

This is the entire script:

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

I am saying that I want to hide the rows based on "Yes" value and then unhide the same range with no criteria. The script hides the rows propertly, but when I change a value to "No" on a already hidden line, the line stays hidden when I unhide them. I am open to ideas if this isn't done correctly. Thanks for your help.

 

[r0bism123]

Anyone out there who can help me with this issue? Your help is greatly appreciated.

Thanks!

 

[My Aswer Is This]

So are you saying if the Toggle Button=True Hide the Rows If Yes and if Toggle Button=False show the rows?

We can try but that is normally not the way to use a Toggle Button

So if Toggle Button=True but cell not Yes do nothing?

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the sheet explain with comments, step by step, what should happen when you press the button, when you change the data in the cell and when you press the button again.

 

[r0bism123]

So are you saying if the Toggle Button=True Hide the Rows If Yes and if Toggle Button=False show the rows?

We can try but that is normally not the way to use a Toggle Button

So if Toggle Button=True but cell not Yes do nothing?

Hi there,

Thank you for your help. Yes, that is how it works. I have a data entry tab that is linked to others 68 tabs in the worksheet that all contain an exact copy of a 184 cell chart of accounts. Each tab is used for different purposes, calculating differently, but they all use the same chart of accounts.. So, I have a helper cell on each of the 68 tabs that says "Yes" to the hide the row if cells value is 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. My issue is that when I enter a value other than 0 to my chart of accounts, and that row is hidden on the 68 other 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 criteria of "Yes" and just have all rows on the 68 tabs unhide regardless of value, yes etc. I hope that helps explain what I am trying to accomplish.

For my edification, why isn't the toggle button supposed to be used this way?

Thank you again for your help.

 

[My Aswer Is This]

Well you said this script I provided caused a error. But all I did was use your exact script and removed the if statement. Their is no evidence in this script that you are using a Toggle Button.
A Toggle button script would normally say. If Toggle Button value=False Or Toggle Button value=False.

I tried that - no luck. It gives me:

runtime error '1004'

unable to set Hidden property of the Range class.

 

[DanteAmor]

For my edification, why isn't the toggle button supposed to be used this way?

This is the Toggle Button