How to hide entire row in Excel if value 0?

August 28, 2009 at 07:45:05
Specs: Windows XP
I have two works---s with the same data (there is a big list of the goods) what I wish to do is: I wish to enter quantity of goods in works---1 but in the works---2 I do not wish to have the rows which have no amount and equals to 0-zero.

in the works---2 I wish to have the list of the goods for which I have entered the amount.

must be mentioned that I have two columns for quantity : one for 1st quality ; and 2nd quality

thank you in advance

See More: How to hide entire row in Excel if value 0?

Report •

August 28, 2009 at 16:44:01
Let's say your list of goods is in column A of Sheet1.
Let's say you are entering a quantity in Column B or C of Sheet1.

Right click the sheet tab for Sheet1 and paste this code into the window that opens.

Whenever you make a change to Column A or B of Sheet1 the code will hide all of the rows in Sheet2 and then scan Columns B and C of Sheet1 and unhide any row in Sheet 2 where there is a value in Column A or B of Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 2 Or Target.Column = 3 Then
  lastRow = Range("A" & Rows.Count).End(xlUp).Row
  Sheets(2).Rows.Hidden = True
   For Goods = 1 To lastRow
    If Cells(Goods, 2) > 0 Or Cells(Goods, 3) > 0 Then _
     Sheets(2).Rows(Goods).Hidden = False
 End If
End Sub

Note: Your requirements only asked about hiding rows with zero quantities. They did not say anything about carrying over the quantities from Sheet1, so the code does not do that.

Report •

October 21, 2009 at 06:12:11
Hi, this is exaclty the solution I need as well, But I cant quite get it to work ..

when you say right click the sheet 1 tab, I"m assuming you meant to select View Code?

Also, do you assume the column has header row "Goods" ?

what else am I missing, as nothing is happening at all when i put this code into the view code box for sheet 1 ...

Many thanks for your help

Report •

Related Solutions

Ask Question