How To Dynamically Set Your Print Area In Excel UPDATE

This is an update to a previous post on setting a dynamic print area, but addresses the issue of determining the height of the print area based on several columns, not just a single column of data.

The previous post on this topic can be found here.

You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.

In a recent post that I referenced above, you can see how to set up your print area dynamically based on the height of the data in a specific column of your data range. A similar concept can be used to adjust the width of the print area.

Recently I had a question come to me asking what if the height of the data range can fluctuate based on several different columns? How do you address that?

Well, as you can see on the video, to set up the the print area for a single column, you would follow these steps:

1) Set any print area for your data range by selecting that range and choosing the Set Print Area from the Print Area drop down on the Page Layout tab of the Ribbon:

That creates an item in your Name Manager called Print Area with that range defined:

2) Next, create a new item in the Name Manager calling what you want (I’ll call mine “DynPrint”), and enter this formula:

=OFFSET($A$1,0,0,COUNTA($C:$C),4)

This will cause the height of the range to be based on the count of items in column C.

3) Now change the formula in the Print Area item in the Name Manager to be =DynPrint

Now you’ve created a dynamic print area based on the data in column C.

So, how do we change this process so that it can adjust the height of the range based on which of column B, C, or D has the most items? Easy! Just use the MAX formula in our OFFSET formula. So what we want to do is replace the COUNTA($C$C) with the following:

=MAX(COUNTA($B:$B),COUNTA($C:$C),COUNTA($D:$D))

And the new formula will now be:

=OFFSET($A$1,0,0,MAX(COUNTA($B:$B),COUNTA($C:$C),COUNTA($D:$D)),4)

And will look like this:

Now, the MAX portion of the OFFSET formula will constantly check which column has the most data and use that value for the height of the range of the OFFSET function to give you the correct dynamic print area!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply