+ Reply to Thread
Results 1 to 5 of 5

Dynamic Print Area becomes static after changing print settings

  1. #1
    Registered User
    Join Date
    04-28-2019
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2013 (home) & 2016 (work)
    Posts
    3

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Dynamic Print Area becomes static after changing print settings

    try to convert range to table
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-28-2019
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2013 (home) & 2016 (work)
    Posts
    3

    Re: Dynamic Print Area becomes static after changing print settings

    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?

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Dynamic Print Area becomes static after changing print settings

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

  5. #5
    Registered User
    Join Date
    04-28-2019
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2013 (home) & 2016 (work)
    Posts
    3

    Re: Dynamic Print Area becomes static after changing print settings

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Set print area for a dynamic range and launch print dialog
    By BONCH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2017, 11:37 PM
  2. how to print using combobox and make dynamic range of a print area
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2014, 07:26 AM
  3. Code and Formula to print dynamic print area ranges
    By stpeter in forum Excel General
    Replies: 1
    Last Post: 10-15-2013, 03:33 PM
  4. Replies: 1
    Last Post: 09-05-2013, 03:32 AM
  5. [SOLVED] Need ‘Set Print Area’ Code for Dynamic Print Range based on Conditions
    By dosbirn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 12:13 PM
  6. Replies: 2
    Last Post: 01-04-2013, 04:22 PM
  7. Dynamic Print Area converting to Static range
    By bnl552 in forum Excel General
    Replies: 1
    Last Post: 06-15-2012, 01:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1