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

  • Thread starter Thread starter JeanPierre Charron
  • Start date Start date
J

JeanPierre Charron

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.
 
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 " " "
Click to expand...

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.
 
hi J.P.,

Am Sat, 31 Jan 2015 16:08:36 +0100 schrieb Claus Busch:
Sub Hide()
Click to expand...

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.
 
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.
 
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.
Click to expand...

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.
 
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.
 
Hi again,

Am Sat, 31 Jan 2015 08:03:38 -0800 (PST) schrieb JeanPierre Charron:
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.
Click to expand...

yes, Saarbrücken is at the border to france and it is 50 km away from
Kaiserslautern where is the biggest army base out of US. You can look
for:
http://www.ramstein.af.mil/


Regards
Claus B.
 
Back
Top