Excel Dynamic Print Area

This Excel tutorial explains how to set dynamic print area.

You may also want to read:

Excel dynamic Data Validation list

Excel dynamic data range

Excel graph dynamic data range

Excel Print Area

Print Area is a function to define the worksheet Range you want to print.

To set a Print Area, select a Range, say A1:B6, then click on Set Print Area

 

Click on FORMULA tab > Name Manager

You will find Range A1:B6 is named as Print_Area, which is the reserved name for setting Print Area.

 

Unfortunately, the Print Area Range has been specified as A1:B6, it will not change dynamically if we input data in row 7.

Create Excel dynamic Print Area – without blank data

Assume that column A does not contain blank row. Replace the formula in Refers to with

=OFFSET(compensation!$A$1,0,0,COUNTA(compensation!$A:$A),2)

If you don’t understand the OFFSET function, click here to view my previous post.

But simply speaking, the syntax of OFFSET function is as below

OFFSET( reference, rows, columns, [height], [width])

Create Excel dynamic Print Area – with blank data

With blank data in column A, we cannot use COUNTA anymore, because COUNTA is to determine how many non-empty cells in column A.

If column A contains blank data AND the last data is a Text, then  use the below formula

=OFFSET(compensation!$A$1,0,0,MATCH(REPT("z",255),$A:$A),2)

If column A contains blank data AND the last data is a Number, use the below formula

=OFFSET(compensation!$A$1,0,0,MATCH(9.99999999999999E+307,compensation!$A:$A),2)

If you are unsure what data type is in the last cell, use

=OFFSET(compensation!$A$1,0,0,SUMPRODUCT(MAX((compensation!$A:$A<>"")*ROW(compensation!$A:$A))),2)

Create Excel dynamic Print Area – with unequal row

Suppose you have the data below. Currently you have data from January to October but it will grow to December in the future, you want to set Excel dynamic Print Area for the growing row.

Replace the formula in Refers to with

=OFFSET(Sheet4!$B$1,0,0,COUNTA(Sheet4!$B:$B),-2)

What the formula means it that, I refer to Range B1 as a starting point of OFFSET, and the height is expanded to the last row of column B, width is expanded one column to the left (-2).  I use COUNTA because I assume all cells in Value should be non-empty.

If some Cells between January and the last month are empty,  type

=OFFSET(Sheet4!$B$1,0,0,SUMPRODUCT(MAX(($B:$B<>"")*ROW($B:$B))),-2)

Now whenever you type something in the Value, you see the Print Area (the border) is instantly resized.

                      

 

Outbound References

https://support.microsoft.com/en-us/kb/830287