Want Excel to not print blank rows.

[ager_merityme]

First, I'm working in an evironment where I cannot use macros.

I have a table showing ages in column A and various information to the right
of the ages with a summary line on row 82. The age column changes based on
actual persons age going up to 100. For instance, if the person is 55 then
the age column only fills down to row 46 using =if(a2<10,a1+1,"").

In this case, is there a way to get Excel, again without macros, to print
the document without the subsequent 'blank' rows where the summary line will
show just below the last actual age?

ks.

 

[ager_merityme]

Yeah, would need it to be automatic as the number of rows to print would
vary. Is it possible to use offset here?

ks.

 

[Chris Wilkinson]

it is not automatic, but if you you go into File>Page Setup... then Sheet
Tab, you will see a input called Print Area. You can specify exactly which
rows and columns you want to print
eg.
A1:E46
which would print just columns A:E from row 1 to 46

 

[ager_merityme]

meant =if(a2<100,a1+1,"")

ks.

 

[Dave Peterson]

Saved from a previous post:

Can you use data|Filter|autofilter to show just the rows that don't appear
empty?

===
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).