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

excel dynamic print area 01

 

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.

excel dynamic print area 02

 

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.

Print_Area_000

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.

Print_Area_02                       Print_Area_03

 

Outbound References

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Excel Dynamic Print Area

  1. trying to set up a dynamic print range to use with a pivot table but this formula is not working
    OFFSET(compensation!$A$1,0,0,SUMPRODUCT(MAX((compensation!$A:$A””)*ROW(compensation!$A:$A))),2)
    Any other suggestions?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *