Can't We Set Print Area with condition?

[Need Help on axis problem in a chart]

Hi,
I would like to set auto print area to an worksheet.
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 that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, 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 from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00

 

[Glenn]

Hi,
I would like to set auto print area to an worksheet.
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 that will change according to the
length of the data on the table? Any help will be appreatiated.

This page has a very long table that imports numbers from other page
(sheet), I only want to print till numbers appear on the table, and dont
want to print the empty cells (blank portion) of the table at the bottom of
the page. If excel auto selects the print area, 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 from the
first sheet. Results shows up in a very long table (500
rows), but only fills up till my data goes, (It varies from 50, 500).
I want to print only till my data (cells with values) skipping the empty
(zeros)
portion of my table.

table would look like this with table (or borders) in it. Dont wana print
zeros at the end. want the excel to select the print area itself.

2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
2 12 323 212 23 32
5 32 321 312 45 23
5 34 542 345 66 64
3 55 343 432 52 51
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00
0 00 000 000 00 00

Try this for Print_Area definition in the Define Name dialog:

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

 

[Raz]

thanks, but I could not find where to add this
please explain where to type this equation or function you wrote. (I am
using excel 2007)
thanks.

 

[Glenn]

I am using Excel 2003, but I think it should still be the same...

Insert / Name / Define

Names in workbook: Print_Area
Refers to: =INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,">0"))

 

[Raz]

found where to add it, thanks
but it gives me this error msg:

The name that you entered is not valid,

Reasongs for this can include;
- the name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel built-in name or the name of another
object in the work book

 

[Glenn]

Did you replace "Sheet1" with the name of your sheet?

 

[Raz]

Yes I did

Did you replace "Sheet1" with the name of your sheet?

 

[Dave Peterson]

It sounds like you mistyped: Print_Area

I would use this version of Glenn's instructions:

Insert|Name|Define

Names in workbook: 'Sheet1'!Print_Area
Refers to: =OFFSET('Sheet'1!$A$1,0,0,countif('sheet1'!$a:$a,">0"),6)

If you have a header row, you'll want to adjust that countif() portion:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a,">0"),6)

And the 6 represents the number of columns to use.

Ps. 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 name.

 

[Raz]

Thanks Dave,
I am using this one:
=OFFSET('Sheet1'!$A$1,0,0,1+countif('sheet1'!$a:$a,">0"),6)

its working now, but i need to fix to apply to appropriate rows and columns.
could you please explain a little more the numbers in the function. I do
have Header Row that I need to print on every page that is Rows 1 to 13

for example.
to print col A to K (this is fixed, wont change)
to check the equation >0 in Col C

in the function
Sheet1'!$A$1,0,0,1
what are these 1, 0, 0, 1 means?

Please take a min to explain these, I appritiate your help. thanks

 

[Raz]

thanks so much for your help, Its working finally, except one problem,
Its not printing the last page, with about 10 rows at the end.

the name I am using is
'Sheet1'!Print_Area
the function I am using is
=OFFSET(Sheet1!$A$1,0,0,1+COUNTIF(Sheet1!$C:$C,">0"),11)

I want it to print till it finds a zero in the column C


Please Help

 

[Dave Peterson]

The =offset() function works like this:

=offset(reference,rows,cols,height,width)

The range is based on A1.

The topleft corner is offset 0 rows and 0 columns from that cell--it stays A1.

The number of rows (height) is 1+countif() rows.
The width is 6 (A:F)

So it sounds like you could add 13 to the number of rows to offset, point at
column C in the countif() portion and make that argument 11 (column K).

=OFFSET('Sheet1'!$A$1,0,0,13+countif('sheet1'!$c:$c,">0"),11)

When you're testing this, you can use:
ctrl-g (or F5 or edit|goto)
and type:
print_area

To see what's selected.

By the way, you can look at excel's help for =offset() to see more info.