How to automatically set print area in Excel template?

[JCMII]

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.

 

[Glenn]

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.

 

[cm]

You can create a dynamic range name called 'Printarea'. Choose Insert Name
Define and in the 'Refers to' at the bottom you will put:

=OFFSET($a$4,0,0,counta($b$4:$b$1000),5)

This example will set the range name from the first cell in column a to the
last non-blank row based on column b, and 5 columns out to the right.

When you select page setup, you will put the range 'Printarea' in the Print
Area box on the 'Sheet' tab.

 

[Glenn]

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<>"",ROW(Sheet1!$A$1:$A$1000),"")))

 

[Dave Peterson]

Saved from a previous post (so you'll have to adjust the sheet names and column
letters and even the columns to print (A:X or B:z????):

If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<>""),ROW(Sheet1!$A$1:$A$1000))

(Make that 1000 big enough to extend past the last possible row.)

Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

If you go into file|page setup, you may find that the print range is changed to
a specific range. And you'll have to reapply the Print_Area name.

 

[JCMII]

Your suggestion seems good, but I seem to have one problem. The COUNTA
function is causing Excel to give an error: "A function in this formula
causes the result to change each time the spreadsheet is calculated..."
Everything I have looked at looks good to me so I don't know why I get this
error. I will continue to try to get it to work. It seems like a good
solution. By the way, I'm using Excel 2007 and saving as Excel 2003 .xlt. I
don't think that should matter, except, I noticed that there seems to be
quite a bit more error checking in 2007. I am going to look to see if the
problem is too much error checking.