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
|