Need help writing Excel Macro to set printing

[roc17]

I'll try to explain here w/o too much ramble:

I have a worksheet that contains several rows and columns. For simplicity sake, let's say that column A is Fruit, column B is quantity, column C is unit price and column D is total ($$). Now.....assume 36 rows each representing a different type of fruit. If I only wanted to print the rows for those types of fruit that were ultimately purchased vs. the entire sheet each time regardless of how many were chosen - How would I write a macro to achieve this and where would I install/store the macro?

Told you I was novice.....

Much appreciated.....

 

[PowerEngineer]

Excel is so flexible that there are almost always many ways to accomplish the same thing. If I were trying to do what you describe, I would not use a macro. Instead I would set up a second sheet on which I would have cell formulas which only copied over those rows from sheet one that had non-zero quantities. You could then just print out this second sheet. Unfortunately, the formulas may be hard to grasp if you're an Excel novice.

Let's say that your list of 100 fruits starting in row 5. I'd start by adding a (hidden) column E formula like this (for cell E8):

=IF($B8>0,MAX(E$1:E7)+1,"")

Copy this formula in column E for rows 5 through 104. You'll see that the result is a counting of the rows that you want printed. In cell F3 put the formula:

=MAX(E5:E104)

On sheet 2, put the numbers 1 through 100 in column A, rows 5 through 104. In column B put a formula like this (for cell B8):

=IF('Sheet1'!$F$3>=$A8,MATCH($A8,'Sheet1'!$E:$E,0),"")

You should see that column B is a list of the row numbers from sheet 1 that have non-zero quantities. Now the rest is easy. In column C put in a formula like this (for cell C8):

=IF($B8>0,INDEX('Sheet1'!A:A,$B8,1),"")

Copy this formula across columns C through F and rows 5 through 104. This should give you the list to print.

One more thing; define the print area on sheet 2 as C5:F104 so that the A and B columns don't show.

I use these kinds of tricks all the time, but I did not actually test these formulas out and therefore apologize for any typos (or brain freezes) you find.

Good luck.

 

[KLin]

Put the data in access. Query the data you want. Print it. Much easier than managing in excel.

 

[roc17]

Power Engineer....thanks for the all the advice...but I couldn't get it to fly.

I copied the sheet from the workbook and emailed to a friend who added some VBA code to it and it really works well. He appears to have added 2 buttons off to the right of the data area (if a row is chosen from the list via input into one of the cells, either number of units or price...not sure...and the 'Print' button is chosen, it segregates only those rows that were needed....the reset button returns the entire list of possibilities with no entries)....my latest problem is I don't know how to add the buttons to the worksheet in Excel (Office 2007). I saw the Macro tab and re-created the code as I found it in his example....but how do I add the Command buttons...or whatever they are?

P.S. the friend isn't available until next week so I can't an answer from him


signed,
learning but still frustrated