Hiding Rows Based on Results in Other Rows

I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

I am new to VB but being forced to learn very quickly due to this project. Any suggestions?

Originally Posted by clange2

I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

I am new to VB but being forced to learn very quickly due to this project. Any suggestions?

First question: How are Subheadings distinguished from the rest of the items? (Is it only that they don't have values in Cols A, C & D?

Second question: (and probably more important) How are people going to be able to enter quantities for those rows that are hidden? Or are you going to set up a button that toggles whether the rows are hidden or not?

The basics of the macro should be to iterate through all rows. (You can find the last row various ways...)

I'm assuming that you start with a Subheading... ie. Row 1 has your column headers, Row 2 has a Subheading, Row 3 is the first row of data (or if no items are currently bought from that supplier, it's another Subheading).

The pseudocode is basically like this:

Please Login or Register  to view this content.

Sorry, don't have much time to expand upon this.

Hope it helps,
Scott