Glenn wrote:
JCMII wrote:
I have created a BOM template. It is used by another program to create
an Excel spreadsheet BOM. The first column is an "Item" number column.
There is a formula in that column to automatically number every row
that has an item in it: =IF(B4="","",ROW()-2). I formatted 1000 rows
so any BOM can fit. My problem is that if you don't " set the Print
Area before you print, you get the 1000 lines printed. I would like to
use my Item number technique to also set the print area automatically
when the BOM is created using the template. Does anyone know how this
might be accomplished? Thansk for the help.


Try this for Print_Area definition in the Define Name dialog:


=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


Adjust the ":F" to reference the rightmost used column in your sheet.


Or, this might be better:

=INDIRECT("A1:F"&MAX(IF(Sheet1!$A$1:$A$1000<"",RO W(Sheet1!$A$1:$A$1000),"")))