Printing a table in Excel

[fanch72]

Hi,
The subject was easy, but missleading.
First I ought to say that the users of my files are very computer illiterates, which is why I am trying to oversimplify those files.
The user inputs a table in sheet1 and must print the resulting table on sheet2. So far no problem. But the input table can be all from 1 row to 200 rows. Since I am not using any macros, all the 200 rows of sheet2 contains formulas. As a result when the user prints (using the only printing way he/she knows, that is the little printer button), he/she prints 14 pages and not only the pages containing data.
I am looking for a way (could be a new "printer button&quot to print only the right number of pages, automatically detected by the system. I would also like to have page 1 of (right number of page) down in the footer and not 1 of 14 as is the case now.
I appreciate any help!
Thanks!!!
Francois

 

[GlennUK]

Well, a simple ( but fragile ) way of doing it is to have a formula for the defined name Print_Area.

Assuming that formulas in column A are "" for those rows NOT printing, and assuming a maximum number of rows of 200, then do this ... do menu command Insert/Name/Define and create a name of Print_Area and have this formula in the Refers To box:
=OFFSET(Sheet2!$A$1,0,0,200-COUNTBLANK(Sheet2!$A$1:$A$200),2)

Now printing should occur for just the area with results in.

WARNING - doing any Page Setup operation will reset the Print_Area name to the Excel default, destroying the formula.

To do this any other way will involve coding macros, I think.

Cheers, Glenn.

 

[GlennUK]

If you don't mind having a simple macro, you can have the creation of the name Print_Area done by macro prior to printing.

In the Workbook macro area create this macro ...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet2&quot.Select
ThisWorkbook.Names.Add Name:="Print_Area", _
RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,200-COUNTBLANK(Sheet2!R1C1:R200C1),2)"
End Sub

The string "=OFFSET etc does not wrap, it should all be on one line.

This will set up the Print_Area defined name before every print.

Glenn.

 

[fanch72]

YES! It works perfect!!! You saved my day! Thanks a lot!!!
Francois