VBA: Hide/Unhide Button - Cell Value - Some Rows to Remain Hidden Always

[Ali Atwi]

Excel 2010:

So I have VBA script that toggles between hiding and unhiding a set of rows.

Private Sub CommandButton1_Click()
Dim myRng As Range
Dim myRng2 As Range
Dim IsHidden As Boolean


Set myRng = Me.Range("ProductionData")
IsHidden = myRng(1).EntireRow.Hidden
Me.Range("ProductionData").EntireRow.Hidden = True
myRng.EntireRow.Hidden = Not (IsHidden)


End Sub


However I have to add another criteria so that a set of rows are to remain hidden all the time.

Which rows are to remain hidden all the time is dependent on the cell value in column A within that range.

The cell values in column A is "Yes" or "No".

Any rows with cell value in column A of "No" are to remain hidden during the toggle.

How can I add or embed an if/and statement into the script so that the hide/unhide also looks for the cell value in column A and just toggles between the rest while any row with cell value column A of "No" remains hidden all the time?

I hope I was clear enough.

Hoping to be pointed in the right direction.

Thank you.

 

[mikerickson]

Have you tried AutoFiltering?

 

[Ali Atwi]

Have you tried AutoFiltering?

No. I wouldn't want the filter arrow to show, if that is the case, then this option wouldn't work for me. If not can you please elaborate further?

 

[Ali Atwi]

I found out that I can not show the arrow, however, will I still be capable of toggling the hide/unhide?

 

[Ali Atwi]

I found out that I can not show the arrow, however, will I still be capable of toggling the hide/unhide?

I came up with this. it still gives me a small error though but works.

Private Sub CommandButton1_Click()


Dim myRng As Range
Dim IsHidden As Boolean


Set myRng = Me.Range("ProductionData")
IsHidden = myRng(1).EntireRow.Hidden
Me.Range("ProductionData").EntireRow.Hidden = True
myRng.EntireRow.Hidden = Not (IsHidden)


If myrange.EntireRow.Hidden = False Then


With ActiveSheet
.Range("A31:A186").AutoFilter
.Range("A31:A186").AutoFilter field:=1, Criteria1:="Yes"
End With
Else
End If




End Sub

 

[Ali Atwi]

Alright I played around with it, I really dont understand what the hell I did, but it worked. Is there any shorter version of this, because all I did was trial and error with the lines, and if someone would care to explain the code for me I would really really appreciate it. (Especially the boolean part, still haven't wrapped my head around it). Thanks.

Private Sub CommandButton1_Click()


Dim myRng As Range
Dim IsHidden As Boolean


Set myRng = Me.Range("ProductionData")
IsHidden = myRng(1).EntireRow.Hidden
Me.Range("ProductionData").EntireRow.Hidden = True
myRng.EntireRow.Hidden = Not (IsHidden)


If Me.Range("ProductionData").EntireRow.Hidden = False Then
With ActiveSheet
.Range("A31:A186").AutoFilter
.Range("A31:A186").AutoFilter field:=1, Criteria1:="Yes"
End With
End If




End Sub