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

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

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

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