Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide/Unhide Multiple Sheets | Excel Programming | |||
Can this be done with HIDE - UNHIDE ? | Excel Programming | |||
Multiple Drop Down Boxes to Hide/Unhide Row | Excel Programming | |||
Hide and unhide rows for multiple areas | Excel Programming | |||
Hide/unhide | Excel Worksheet Functions |