Dynamic Print Area becomes static after changing print settings

[nchristie84]

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:

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

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

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!

 

[mole999]

Just a thought. I created a named Dynamic Range and recorded this

    Application.Goto Reference:="DynamicRange"
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$244"

 

[nchristie84]

Just a thought. I created a named Dynamic Range and recorded this

    Application.Goto Reference:="DynamicRange"
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$244"

Yes, unfortunately this workbook needs to remain VBA-free in order to remain compatible with iOS devices. I'm struggling to find a way to keep the array dynamic using only formulas / named ranges.

I'm also struggling to understand why this Excel bug exists in the first place, and also why there don't seem to be any obvious solutions anywhere?!