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

=3DOFFSET(Sheet1!$A$11,1,0,COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!=AD$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.
=3D{MAX((ROW(1:10000)*(A1:A10000<"")))} to figure out the bottom row
=3D{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
=3DOFFSET(Sheet1!$A11,1,0,bRow,rCol)

This seems to work but requires two cells in the worksheet. Any other
suggestions?

- John