Hide rows if formula result is zero

  • Thread starter Thread starter John
  • Start date Start date
J

John

My spreadsheet layout is as follows:

A B C
Apples 50 20
Oranges 0 0
Bananas 15 0

I need excel to hide the rows when the results of the formulas in both
column B and C are zero. Oranges would be hidden, bananas would not. If the
cell in B or C is empty, I don't want it hidden. All formulas in cells I
want hidden start with =sumif. I am using Excel 2000. Thanks for the help.
 
Try this on a copy of your workbook

Select the cells and run this

Sub DeleteRows()
Dim r As Long
With Selection
For r = .Cells.Count To 1 Step -1
If .Cells(r, 2).Value = 0 And .Cells(r, 3).Value = 0 Then
.Cells(r, 2).EntireRow.Hidden = True
End If
Next
End With
End Sub
 
Sorry I pasted the wrong example in this thread
select the cells in A:C and try it

SubTest()
Dim r As Long
With Selection
For r = .Rows.Count To 1 Step -1
If .Cells(r, 2).Value = 0 And .Cells(r, 3).Value = 0 Then
.Cells(r, 2).EntireRow.Hidden = True
End If
Next
End With
End Sub
 
somewhat simple task to do, first start by selecting the
cells you want the 0 values hidden on (you can multiple
select cells by holding the 'ctrl' button). Move your
mouse pointer over one of the cells you have selected,
and right click with your mouse. From the drop-down list,
select 'format cells...'. The format cells dialog box
will come up. At the top of the box, left click on
the 'number' tab. At the bottom of the list you will see
the word 'custom'. left click on it. The right portion of
the box will now have the word 'type' and just below that
a white box in which you will highlight whats in that box
and add the following : 0;-0;;@
The next time you need to hide zero values, follow the
instructions ive provided except that once you click on
the word 'custom', the 0;-0;;@ will be in the bottom of
the list thats found at the bottom right of that box.
this formula will also work for hiding zero value decimal
points (type in : 0.0;-0.0;;@ )and also zero percents
(type in 0%;-0%;;@ ).
 
Back
Top