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 mo
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

Using named range to extend print area for variable number ofcolumns
 

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 mo
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 mo
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

Using named range to extend print area for variable number ofcolumns
 

On Apr 10, 9:41*am, Dave Peterson 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 mo
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

Using named range to extend print area for variable number ofcolumns
 

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 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 mo
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