Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

If all cells in Row X Columns B, C, D and E, are either Null, Empty, Blank or equal to 0 (Zero) then Hide/Unhide Row from Button_Click.
..
Simulation Test
Col B Col C Col D Col E
100 50 200 350 Not Hideable on Button_Click
=50 50 0 0 Not Hideable " " "
0 0 0 0 Hide / Unhide " " "
0 Hide / Unhide " " "
Thank you for your help.
JP.

Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

Hi J.P.,

Am Sat, 31 Jan 2015 06:55:20 -0800 (PST) schrieb JeanPierre Charron:

Simulation Test
Col B Col C Col D Col E
100 50 200 350 Not Hideable on Button_Click
=50 50 0 0 Not Hideable " " "
0 0 0 0 Hide / Unhide " " "
0 Hide / Unhide " " "

try:
Sub Hide()
Dim varData As Variant
Dim i As Long

With ActiveSheet
varData = Intersect(.UsedRange, .Range("B:E"))
For i = 1 To UBound(varData)
If WorksheetFunction.Sum(Application.Index(varData, i, 0)) = 0
Then
.Rows(i).Hidden = True
End If
Next
End With
End Sub

Sub Unhide()
With ActiveSheet
.UsedRange.EntireRow.Hidden = False
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

hi J.P.,

Am Sat, 31 Jan 2015 16:08:36 +0100 schrieb Claus Busch:

Sub Hide()

if you want to do it with a button insert an activeX button into the
sheet and change its caption to Hide.
Then insert following code into the CommandButton1_click event:

Private Sub CommandButton1_Click()
Dim varData As Variant
Dim i As Long

With ActiveSheet
If .CommandButton1.Caption = "Hide" Then
varData = Intersect(.UsedRange, .Range("B:E"))
For i = 1 To UBound(varData)
If Application.Sum(Application.Index(varData, i, 0)) = 0 Then
.Rows(i).Hidden = True
End If
Next
.CommandButton1.Caption = "Unhide"
Else
.UsedRange.EntireRow.Hidden = False
.CommandButton1.Caption = "Hide"
End If
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

Thank you again, but if cells equal -50, 50,0,0 the sum would equal 0 but this row
would not would not be hideable because I have negative and positive values
cancelling each other in Col B and C in this case.
..
You have helped me a lot in the past. I wished you were close enough to take
you to a good restaurant. I mean it. What is your geographical area ?
Have a good day,
JP.

Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

Hi J.P.,

Am Sat, 31 Jan 2015 07:38:43 -0800 (PST) schrieb JeanPierre Charron:

Thank you again, but if cells equal -50, 50,0,0 the sum would equal 0 but this row
would not would not be hideable because I have negative and positive values
cancelling each other in Col B and C in this case.

try:
Private Sub CommandButton1_Click()
Dim LRow As Long
Dim i As Long
Dim myCount As Long

With ActiveSheet
If .CommandButton1.Caption = "Hide" Then
LRow = .UsedRange.Rows.Count
For i = 1 To LRow
myCount = WorksheetFunction.Count(.Range(.Cells(i, 2), _
.Cells(i, 5)))
If myCount = 0 Or WorksheetFunction.CountIf _
(.Range(.Cells(i, 2), .Cells(i, 5)), 0) = myCount Then
Rows(i).Hidden = True
.CommandButton1.Caption = "Unhide"
End If
Next
Else
.UsedRange.EntireRow.Hidden = False
.CommandButton1.Caption = "Hide"
End If
End With

End Sub

I am from Saarbrücken in Germany ;-)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hide / Unhide each row from StartRow to LastRow for multiple conditions
 

I take note. I think you are just across form the North East corner of France
but I will not be in that area until October 2016.
I will see you later.