Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dynamic Print Range Help
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 |
#2
|
|||
|
|||
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" wrote in message oups.com... 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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Dynamic Range Problem | Excel Discussion (Misc queries) | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions |