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

[Pierre]

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]

Create a new name (Sheet1!LastCol) defined like:

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]

Create a new name (Sheet1!LastCol) defined like:


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]

You're welcome.

This is really a neat technique, huh?

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