Dynamically change print area

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John R - 05 Sep 2005 17:19 GMT

How can I dynamically change the print area without using a VB macro.
The rows of data in the spreedsheet A1:E1200 varies each month.
I.E. month 1 there is data in A1:E800, month 2 there is data in
A1:E1100, Etc.
There are blank rows & columns in the area that I want to print.
Any help would be appreciated.

Signature

John R

Reply to this Message

Don Guillett - 05 Sep 2005 20:02 GMT

try
insert>name>define>name it
Print_Area
in the refers to box type
=offset($a$1,0,0,counta($a:$a),5)
touch OK>test it

Signature

Don Guillett
SalesAid Software
donaldb@281.com

>
> How can I dynamically change the print area without using a VB macro.
[quoted text clipped - 3 lines]
> There are blank rows & columns in the area that I want to print.
> Any help would be appreciated.

Reply to this Message

Dave Peterson - 05 Sep 2005 21:16 GMT

I think this will fail with those blank rows in the range to print.

> try
> insert>name>define>name it
[quoted text clipped - 21 lines]
> http://www.excelforum.com/member.php?action=getinfo&userid=26985
> > View this thread: http://www.excelforum.com/showthread.php?threadid=401955

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 05 Sep 2005 21:18 GMT

Maybe...

Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

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

(Make that 2000 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,5)

That last 5 represents the last column to print (A:E).

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

> How can I dynamically change the print area without using a VB macro.
> The rows of data in the spreedsheet A1:E1200 varies each month.
[quoted text clipped - 8 lines]
> John R's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26985
> View this thread: http://www.excelforum.com/showthread.php?threadid=401955

Signature

Dave Peterson

Reply to this Message