Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Dynamic print range problem

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening all,

    I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

    if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

    ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

    am I missing something ? I want the print range to expand and contract accordingly.

    many thanks
    Chris

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 14:58, Chris Davison wrote:
    Evening all,

    I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

    if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

    ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

    am I missing something ? I want the print range to expand and contract accordingly.

    many thanks
    Chris
    [img]/board/images/smiles/icon_smile.gif[/img]
    Chris,

    Can you indicate the current print range: Is it A1:G400? If so, File|Print Are|Set Print Area should simply work.

    Aladin

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks Aladin.... but no, that's what I thought also : I entered the print range as "=test" and later as "test" but it replaces this name with the actual range ie A1:G400 which doesn't change when the named range contracts...

    weird

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-07 14:58, Chris Davison wrote:
    Evening all,

    I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

    if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

    ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

    am I missing something ? I want the print range to expand and contract accordingly.

    many thanks
    Chris
    [img]/board/images/smiles/icon_smile.gif[/img]
    Define the name, Print_Area, using the same formula that "test" refers to.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    guess what ?

    it works !

    thanks Mark, thanks Aladin


  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 15:37, Chris Davison wrote:
    guess what ?

    it works !

    thanks Mark, thanks Aladin

    [img]/board/images/smiles/icon_smile.gif[/img]
    Not so fast.

    If you activate File|Print Area|Set Print area and you select the area of interest by selecting column indicators A thru G, you'll have the same result as when you define Print_Area to refer to an OFFSET formula.

    I remember to have proposed at the old board using an OFFSET formula as reference to the reserved name "Print_Area" and being corrected by a one-time visitor how unnecessary that move was. Whence my question.

    Aladin


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks Aladin,

    if I add further info in columns H,I,J,K (row1), the print area doesn't update if I use your non-offset method, it's not dynamic; I'd have to update it manually.

    (using the page break preview mode, you can actually see the ranges increase and decrease with the Print_Area = offset etc method)

    are you saying someone advised you it was not necessary to use the offsetted print_area ? I'd say they were wrong, the file / print area / set print area is static, not dynamic...




    edit - it updates if you *insert* into the range, but not if you augment by just including (ie typing or pasting) extra data into further columns

    [ This Message was edited by: Chris Davison on 2002-04-07 16:15 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 16:08, Chris Davison wrote:
    thanks Aladin,

    if I add further info in columns H,I,J,K (row1), the print area doesn't update if I use your non-offset method, it's not dynamic; I'd have to update it manually.

    (using the page break preview mode, you can actually see the ranges increase and decrease with the Print_Area = offset etc method)

    are you saying someone advised you it was not necessary to use the offsetted print_area ? I'd say they were wrong, the file / print area / set print area is static, not dynamic...




    edit - it updates if you *insert* into the range, but not if you augment by just including (ie typing or pasting) extra data into further columns

    [ This Message was edited by: Chris Davison on 2002-04-07 16:15 ]
    Rowwise it's dynamic, really. You have to select the range by using column selector. If your range is changing columnwise, select the range using row selector. That works too!

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-07 16:37 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not convinced the two methods are the same

    I can only select by rows or by columns, but not both...... if I select by column to include all rows, my columns are static; if I select by rows to include all columns, my rows are static !

    if I click on the top left "all", that's basically the deafult everything, not the same as, say, a print range defined by a dynamic named range (Print_Area)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-07 16:58, Chris Davison wrote:
    I'm not convinced the two methods are the same [img]/board/images/smiles/icon_smile.gif[/img]

    I can only select by rows or by columns, but not both...... if I select by column to include all rows, my columns are static; if I select by rows to include all columns, my rows are static !

    if I click on the top left "all", that's basically the deafult everything, not the same as, say, a print range defined by a dynamic named range (Print_Area)
    Hee, I'm not saying that both methods are eq. I didn't see your dyn range formula, so I just wanted to signal the easy way in case your situation might fit it.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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