creating macro to print all columns but only rows with data

Hello all,


I've created a template in excel to calculate various results (mean, standard deviation, etc.) I have column A dedicated to numbering (1-100); B-O for users to enter their own information, columns P-W have various calculation formulas. Rows 12-111 are where data (analyte names and associated values for calculations) will be entered. My co-workers will use this, so I want this to automatically print the appropriate selection without them having to do a print selection. They may have anywhere from 1-100 analytes to calculate. The Sheet name is MDL Calc Form.

I always want all columns to print and I always want the first 11 rows to print, but I only want the rows from 12:111 to print if data is entered in merged column B:C (Analyte Name). Unfortunately for me, since I have formulas extending down to 111, the macro I was trying to use reads that as a value and I cannot figure out a way around this.

So, always print area: A1-A11:W1-W11
Only print A12-W12 if BC12 has value
Only print A13-W13 if BC13 has value, etc. etc.

Also, I think I can save the workbook to automatically print 'fit all columns on one page' so it always prints that way, but in case I am wrong please include that also. I couldn't figure out a way to attach the excel file either.

I would be forever grateful to anyone who could write me a macro to do this. I am utterly lost and have little coding knowledge. Please help!

Thank you!!
jkarns

[attach]xlsm.gif DRAFT L40.20 r0 MDL Calculation Form with Summary.xlsm[/attach]

Let's try this maybe? Well that's one problem down! Thanks Keebellah!

So it still printed all the rows. Is it possible to make the macro read if a name is typed into column BC, row 12,13,14 etc. it will only print that row? So in other words, if there are only two analytes entered, I only want it to print those two rows (plus the first 11 rows.) Any suggestions?

Awesome! It worked on hiding all the cells! The problem is, I don't want to run the macro until the data has been entered and ready to print. So type in all data, save as (whatever data this corresponds to), then when whoever goes to print, I want the macro to run. I had to copy the macro into the original file I had. I attached a copy of it in the thread so I don't have the printer icon. Everything else works, but the macro starts as soon as the file is opened. Can this be avoided?

I'm getting an error Worksheets ("MDL Calc Form").Select line

I added the Prepare_MDL_Calc_Form

I get Run-Time error '9': Subscript out of range when I hit the print button. It worked on the original sheet, just not when I copied it.

My problem has been solved. Thank you Keebellah for all of your help. I am very grateful!!

I think I finally got it working. Thank you for all of your help! I truly appreciate it!