Using named range to extend print area for variable number of columns

worksheet functions >> Using named range to extend print area for variable number of columns I've used the following method from D. Peterson to automatically set
the print area to extend to the last row which contains actual data.

Now, I'm faced with a similar situation which the 'columns' are now
the variable component.
Rows to be printed begin at A1:A18 and will always include columns A
and B; there may be as many as 40 columns to include, or as few as the
1st two

These are the 2 components of the named ranges which will set the
number of rows(for reference):>>

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

Any ideas? I'd like to keep the named range concept if possible
Thanks for any thoughts.

Pierre

Excel485

worksheet functions >> Using named range to extend print area for variable number of columns Create a new name (Sheet1!LastCol) defined like:
=LOOKUP(2,1/(Sheet1!$1:$1<>""),column(Sheet1!$1:$1))
(where row 1 is the "defining" row -- maybe headers???)

> Then once more:
> Insert|Name|Define
> Names in workbook: Sheet1!Print_Area
> Use this formula
> Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,LastCol)

And remember not to go into page setup to override your Print_Area.

Pierre wrote:
>
> I've used the following method from D. Peterson to automatically set
> the print area to extend to the last row which contains actual data.
>
> Now, I'm faced with a similar situation which the 'columns' are now
> the variable component.
> Rows to be printed begin at A1:A18 and will always include columns A
> and B; there may be as many as 40 columns to include, or as few as the
> 1st two
>
> These are the 2 components of the named ranges which will set the
> number of rows(for reference):>>
>
> 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).>>
>
> Any ideas? I'd like to keep the named range concept if possible
> Thanks for any thoughts.
>
> Pierre

--

Dave Peterson

worksheet functions >> Using named range to extend print area for variable number of columns On Apr 10, 9:41=A0am, Dave Peterson <EMail@HideDomain.com> wrote:
> Create a new name (Sheet1!LastCol) defined like:
> =3DLOOKUP(2,1/(Sheet1!$1:$1<>""),column(Sheet1!$1:$1))
> (where row 1 is the "defining" row -- maybe headers???)
>
> > Then once more:
> > Insert|Name|Define
> > Names in workbook: =A0Sheet1!Print_Area
> > Use this formula
> > Refers to: =A0=3DOFFSET(Sheet1!$A$1,0,0,lastRow,LastCol)
>
> And remember not to go into page setup to override your Print_Area.
>

>
>
> >
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave, thanks so much, once again!

Pierre

worksheet functions >> Using named range to extend print area for variable number of columns You're welcome.

This is really a neat technique, huh?

(I saw it in one of the free files MS offers.)

Pierre wrote:
>
> On Apr 10, 9:41 am, Dave Peterson <EMail@HideDomain.com> wrote:
> > Create a new name (Sheet1!LastCol) defined like:
> > =LOOKUP(2,1/(Sheet1!$1:$1<>""),column(Sheet1!$1:$1))
> > (where row 1 is the "defining" row -- maybe headers???)
> >
> > > Then once more:
> > > Insert|Name|Define
> > > Names in workbook: Sheet1!Print_Area
> > > Use this formula
> > > Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,LastCol)
> >
> > And remember not to go into page setup to override your Print_Area.
> >
>
> >
> >
> > >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> Dave, thanks so much, once again!
>
> Pierre

--

Dave Peterson