Dynamic Print Area

[MGall]

I am trying to set the print area to print only the area of the worksheet that contains text. Some cells contain formulas, so if you print the worksheet, 3 pages will be printed while only one may actually contain information.

I created a named range 'MyPrintArea' and entered the following formula:

=OFFSET(Sheet1!$A$1,0,0,22+COUNTIF(Sheet1!$A$23:$A$112,">0"),12)

This part works fine, and will return the correct range everytime as well as update as the sheet changes. The problem arises when i go to set the 'Print_Area' to equal my dynamic range 'MyPrintArea'. Instead of the named range, excel automatically updates the "refers to" area to the range that 'MyPrintArea' is equal to at that time. Ex:

=MyPrintArea
changes to
=Sheet1!$A$1:$L$24

I have tried entering the offest formula directly into 'Print_Area' and get the same result. I have seen examples where this works fine for other users, and 'Print_Area' remains equal to the named range and updates along with it.

Any thoughts?

[Andy Pope]

Did you create MyPrintArea so the scope was sheet level rather than workbook?

[MGall]

Thanks for the reply.

Yes, I created it so it applies to the sheet. I also tried it with the scope at the workbook level, and that didn't work either.

[MGall]

I think my issue was with the page formatting. When I switched the view from page layout to normal, the fomula started working... but now if I put it back into the page layout the formula won't work again. I guess I can live with the normal view, but if anyone has a workaround or can tell me why this happens I'd appreciate it.

[Andy Pope]

Can confirm changing the view to Page Layout causes the print_area name to be converted to actual cell references.