Dynamically change print area

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.


--
John R
------------------------------------------------------------------------
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

Don
Mon Sep 05 14:02:40 CDT 2005

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"John R" <John.R.1uwb6a_1125939900.5381@excelforum-nospam.com> wrote in
message news:John.R.1uwb6a_1125939900.5381@excelforum-nospam.com...
>
> 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.
>
>
> --
> John R
> ------------------------------------------------------------------------
> 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
>

Dave
Mon Sep 05 15:16:54 CDT 2005

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



Don Guillett wrote:
>
> 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
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "John R" <John.R.1uwb6a_1125939900.5381@excelforum-nospam.com> wrote in
> message news:John.R.1uwb6a_1125939900.5381@excelforum-nospam.com...
> >
> > 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.
> >
> >
> > --
> > John R
> > ------------------------------------------------------------------------
> > 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
> >

--

Dave Peterson

Dave
Mon Sep 05 15:18:42 CDT 2005

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

John R wrote:
>
> 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.
>
> --
> John R
> ------------------------------------------------------------------------
> 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

--

Dave Peterson