Dynamic Print Area becomes static after changing print settings

nchristie84

New Member
Joined
Apr 28, 2019
Messages
2
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:

222b3b35-a803-415e-8c84-1438b2bc0be5


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

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

05e9997a-0d2a-4469-8670-a00d853bc090


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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Just a thought. I created a named Dynamic Range and recorded this

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

nchristie84

New Member
Joined
Apr 28, 2019
Messages
2
Just a thought. I created a named Dynamic Range and recorded this

Code:
    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?!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,108
Messages
5,628,756
Members
416,336
Latest member
Eferiveros

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top