How do I set Auto Print Area?

[Need Help on axis problem in a chart]

Hi,
I would like to set auto print area to an worksheet, that changes(# of rows)
for different project.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area to change according to the length
of the page or number of rows? Any help will be appreatiated.

Raz

 

[Spiky]

Hi,
I would like to set auto print area to an worksheet, that changes(# of rows)
for different project.
I have fixed width (number of columns), but length (number of rows with
values) changes, anywhere from 50 to 500.
Is there any way I can make the print area to change according to the length
of the page or number of rows? Any help will be appreatiated.

Raz

2 thoughts:

Don't set a print area. Excel automatically includes everything in the
sheet by default.

If that doesn't come out right for other reasons, go into Page Setup
and select Fit To. Make sure it is a "1" in the Wide field. Then
highlight the number in the Tall field and delete it, leave it blank.
Click OK.

 

[Need Help on axis problem in a chart]

Thanks Spiky, but sorry it didnt help.
my page would have a very long table that imports numbers from other page
(sheet), I only want to print till the numbers appears on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page.
If excell auto selects, then it prints the whole table.


HERE IS HOW MY PAGE LOOKS

i have 2 sheets, on the first sheet i do the calculations.

second sheet is the one i need to print, which gets the results of my
calculation from the first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (may be 50, sometimes 200, it
varies).
I want to print only till my data (cells with values) skipping the empty
portion of my table.

 

[Spiky]

Oh, I see.

Make a print area. Go into Namesefine. Select the Print_Area name
and change its reference. Something like:

=INDIRECT("Sheet1!$A$1:$G$"&Sheet1!$H$1)

So it will print the columns through G and the rows through whatever
number is in H1. And then you put a formula to figure out the last row
in H1. You may be able to just plug that formula into the Name, as
well.

 

[Raz]

sounds alright,
but how do I put the formulla in there, can u give a little more
explanation, or an example please.

 

[Spiky]

sounds alright,
but how do I put the formulla in there, can u give a little more
explanation, or an example please.

In xl2003, there is a dialog box called "Define Name". Accessible via
the Insert menu or CTRL-F3. If you have already set a print area as I
recommended, it will list it in the window. Click on it once and the
range will be shown at the bottom of the box. Then you can change the
range (or you can paste something you've copied, like my example
above) and just click OK to get out of the box. TIP: don't use arrow
keys when typing in this box. Use the mouse if you have to move to a
different part of the formula to type. TIP2: Print_Area ranges must
have the sheet name in them to work properly, I believe. So use
"Sheet1!A1", not just "A1".

I don't know how this may have changed in xl2007.

 

[Raz]

thanks

In xl2003, there is a dialog box called "Define Name". Accessible via
the Insert menu or CTRL-F3. If you have already set a print area as I
recommended, it will list it in the window. Click on it once and the
range will be shown at the bottom of the box. Then you can change the
range (or you can paste something you've copied, like my example
above) and just click OK to get out of the box. TIP: don't use arrow
keys when typing in this box. Use the mouse if you have to move to a
different part of the formula to type. TIP2: Print_Area ranges must
have the sheet name in them to work properly, I believe. So use
"Sheet1!A1", not just "A1".

I don't know how this may have changed in xl2007.

 

[Gord Dibben]

Set up a dynamic printrange.

See Debra Dalgleish's site for dynamic range naming.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP