Dynamically Setting a Print Area

I like the solution presented in this tip (I suggested this approach in my reply to help-wanted, but I probably wasn’t the only one). It is relatively simple, does what Travis wanted, and does not “contaminate” the spreadsheet with helper cells. What Allen did not mention was that the address of the cell A1 in the formula must be entered as an absolute address, i.e., Sheet1! $A$1; if it is entered as the relative address the Print_Area is not set properly, and the approach does not work (why? – I don’t know).

I also suggested another approach, that may not be as elegant, but potentially may have additional advantages. The steps are as follows:
1. Select a 20-rows by 5-columns range somewhere on the sheet, where it would not interfere with your data. Set the Print Area to that range.
2. In the top left corner of that print area enter =OFFSET(INDIRECT($A$1),0,0,20,5). This formula will spill and populate Print_Area with a copy of what you want to print, and when you print the sheet, it is what will be printed. As in the tip, I assume that the calculated cell address is in A1.
3. If using an older version of Excel that does not automatically spill into Array, you will need to enter the above formula as an Array Formula (Ctrl+Shift+<ENTER>) covering the whole Print_Area range.

The benefit of this approach is that you can format the data that is in the Print_Area any way you want, independently of the format of original data. Additionally, you can add rows above to add column labels and possibly the title, and add a column to the left for row labels. Those do not have to be static but can also depend on the content of A1. If you add rows and columns, obviously you will need to adjust the print area to include them as well.

There is one disadvantage of this approach: if any of the original cells are blank, they will show in the print area as zeros, probably not what you would want. It can be easily overcome by slight modification of the formula entered in step 2 as follows:
=IF(OFFSET(INDIRECT($A$1),0,0,20,5)="","",OFFSET(INDIRECT($A$1),0,0,20,5))
See screenshots of my test workbook (see Figure 1 below) (see Figure 2 below)