Dynamic Print Area becomes static after changing print settings

Hi all,

I have a dynamic print area (using OFFSET formula) that applies to a table with up to 200 rows, which will usually have only a few dozen or so rows actually populated with data. Hence why I made it dynamic, so that the user won't be printing out multiple pages of a blank table after their first or second page of actual data.

I've now noticed that any time the user adjusts any of the print settings (e.g. switches from portrait to landscape, or vice versa), the dynamic range in the Name Manager now refers to a static range. Meaning that once the table data has adjusted the number of rows containing data, Excel will continue to only print the range that existed prior to the change of print settings.

For example, let's say the table currently has 7 rows populated with data. The user prints this out, but first changes the orientation from portrait to landscape. The next user edits the table so now there are 12 rows populated with data. They go to print it, but can only print the first 7 rows. The dynamic print area has become static for just the first 7 rows thanks to the actions of the first user.

I replicated this with a dummy table (see attached workbook to try it for yourself):

2019-04-27.png

Notice that the Print_Area refers to the 'DynamicRange' I've created.

Now, after adjusting the page setup from portrait to landscape:

2019-04-27 (1).png

The Print_Area now refers to the static range that the dynamic range currently occupies.

I've already tried inputting the OFFSET formula directly into the 'Refers To' field of the Print_Area, skipping the DynamicRange reference altogether, but it still keeps happening. I've also tried using variations of INDIRECT formulas, but Excel keeps converting whatever formula I enter to a static range whenever the page setup is altered.

I'm aware that there is a VBA solution for this, and am familiar with how to implement it. However, this workbook has to stay macro-free, as it also needs to be opened and used on iOS devices such as iPads, which are incompatible with macro-enabled workbooks.

Any help is greatly appreciated!

try to convert range to table

Yes that seems to work, but only because the table the print area refers to is already static. Does anyone know how to make a table size adjust dynamically?

What do you mean?
I changed layout, added and deleted rows. Print area was dinamic.

The table I'm starting with already has 200 rows; this is fixed as such, and the user will not be able to delete rows. Only some of the rows will actually be populated with data, and the blank rows should ideally not be included in the print area. If I refer the print area to the table name, it will include the blank rows as well.