Print ranges can accept dynamic arrays. This means you can set up print ranges that automatically expand or contract based on dynamic array spill ranges.

To see how this can work examine the spill range in cell A1 in the image below.

This spill range is created by a SEQUENCE function.

Dynamic Print Ranges

To create a flexible Print range we can select the spill range and click the Set Print Area icon on the Page Layout tab – image below. (I have the icon on my Quick Access Toolbar).

After setting the print area we can edit the Print_Area range name to change it to the spill range reference.

In the Formulas Tab click the Name Manager icon and click the Print_Area range name for the sheet. In the Refers to box change the reference to the Spill range reference – in this case $A$1# – see image below.

That’s it – the print range name updates with the spill range.

You can see the differences when you use the Page Break Preview view – see image below.

Flexible Dynamic Print Ranges

You can even create a flexible print area based on two spill ranges.

In the image below has two spill ranges spilling down. They are different lengths.

You can create a print range by  combining the spill range references separated by the colon.

=Range!$A$2#:Range!$D$2#

You can see the various print ranges created as we change the spill range in the images that follow.

Spill range references can provide an easy way to create dynamic print ranges.