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

=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

[Gary Brown]

WaxWing,
How about this little macro...
'/==================================/
Sub PivotTableAddress()
Dim strPTA As String
strPTA = _
Worksheets("Sheet1").UsedRange.Address
strPTA = "$A$10" & _
Right(strPTA, Len(strPTA) - _
WorksheetFunction.Find(":", strPTA) + 1)
ActiveSheet.PageSetup.PrintArea = strPTA
End Sub
'/==================================/

This assumes that...
1) There is only one pivot table on the worksheet
2) When you run this macro, you are in the worksheet
with the pivot table on it
3) The information to be printed starts at 'A10'

HTH,
Gary Brown


"waxwing" <[email protected]> wrote in message
news:[email protected]...
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.
={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)

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

- John

[waxwing]

Thanks, Gary. Unfortunately, I need the range to dynamic after the
code is complete. If the user, changes the pivot table (perhaps,
selects a new value for one of the page fields) and the size of the
table changes, the print range needs to change too.

- John