Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows if formula result is zero
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows if formula result is zero
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "John" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows if formula result is zero
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "John" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows if formula result is zero
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%;;@ ). -----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide rows if nil result of formula in specific column | Excel Discussion (Misc queries) | |||
Hide a formula result until cell has been filled | Excel Discussion (Misc queries) | |||
Hide #VALUE! in result of array formula | Excel Worksheet Functions | |||
Hide formula result values until all components are entered | Excel Discussion (Misc queries) | |||
Help - Need Formula To Hide Rows | Excel Discussion (Misc queries) |