Dynamic Print Range Help

[waxwing]

I need away to dynamically change the print range for a sheet that
contains a pivot change in which the number of columns and rows may
change. I've figured out how to do this if the first column of the
pivot table includes continguous data but it doesn't work if some of
the cells are blank.

To give you an idea of my approach (though yours maybe completely
different) here's what I've done.

Assumptions -
Pivot table Page fields in Row 10 (this doesn't change)
Top of table is in Row 12 so from column A through end of table, all
cells have data.
Column A, Row 12 through the word "Grand Total in column all contain
data.

Print range is set by adding an Offset formula:

Insert > Name > Define > Print_Area

=OFFSET(Sheet1!$A$11,1,0,COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A1:$A11),COUNTA(Sheet1!$12:$12))

How it works:
OFFSET(Sheet1!$A$11,1,0 - starts the range one cell below A11
(which is the bottom of the print title range)
COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A1:$A11) - determines the
number of filled rows in the print range not including the print title
rows of A1:A11.
COUNTA(Sheet1!$12:$12) - determines the number of columns in the
pivot table

As mentioned, this works fine as long as there are no blanks in the
print area of column A.

Another approach would be to use the last column (which may change)
since there will always be totals in that column. I've been able to
use array formulas in the sheet itself to determine the bottom row and
rightmost column.
Bottom row first column =
{(ADDRESS(MAX((ROW(1:10000)*(A1:A10000<>""))),COLUMN(A:A)))}
Rightmost column, top row =
{(ADDRESS(ROW(13:13),MAX((COLUMN(A:BB)*(A13:BB13<>"")))))}
But I haven't figured out how to extract just the bottom right cell and
incorporate it into the Print_Area named range.

Any help would be appreciated.

- John

 

[waxwing]

Well, a minute after I posted, I came up with a partial solution. My
solution requires that two array formulas are present in the worksheet
and then referenced by the named range Print_Area. I would prefer not
to have these formulas in the sheet at all so I'm still hoping for a
better solution.

Here is my temporary solution.

Changed the array formulas above to:
={MAX((ROW(1:10000)*(A1:A10000<>"")))} to figure out the bottom row
={MAX((COLUMN(A:BB)*(A13:BB13<>"")))} to figure out the rightmost
column

Put these formulas in cells named bRow and rCol, respectively.

Changed the Print_Area named range to reference
=OFFSET(Sheet1!$A11,1,0,bRow,rCol)

Note I've only had success editing the named range view the menu
Insert>Name>Define. Entering the formula in the Print Setup or
creating another named range and referring to that in the Print Setup
does not seem to work.

Still hoping for some guidance for a better solution.

- John