Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Dynamic Print Rage

    Loungers, has any one got any suggestions on the best way to set up a dynamic print range for the sample attached.

    The first cell and the width will not change however the depth will change based on the data entered into the sheet.

    Any suggestions would be very much appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Here's one solution:
    =OFFSET(Sheet1!$D$6,0,0,COUNTA(Sheet1!$D$6:$D$8009 ),COUNTA(Sheet1!$D$6:$R$6))
    DynamicPrintRng.JPG
    Assumptions.
    1. You only want to print D through K
    2. There will be no blank lines.
    3. There will be less than 8000 entries.

    Of course the formula can be adjusted to change these assumptions.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thank you for the quick reply. works well the only problem I have is that the data starts to populate from d12. The rows from 6 to 11 is basically a header for the report.

    Any thoughts on how these rows can be included and the data from d12 that is the dynamic range. I hope that makes sence

    Thanks for your assistance

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verda,

    Could you post a test sheet with some data filled in including the headers so I can see what the problem is? I don't quite understand.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for the reply - sorry the message wasnt clear.

    Please see the attached file, I hope that helps

    Much appreciated.
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verda,

    Ok, I've revised the formula to account for the 5 line header box which was causing problems with the counts.
    The new formula is:
    =OFFSET(Sheet1!$D$6,0,0,COUNTA(Sheet1!$D$11:$D$800 9 )+5,COUNTA(Sheet1!$D$11:$K$11))
    Enter it in the Defined Names Box
    DefinedNamesBox.JPG
    Then Enter the name in the PrintArea box.
    PrintRangeBox.JPG
    The result in Print Preview
    PrintPreviewofSheet.JPG
    Note these pictures are all done in Excel 2010 but all the stuff will work with 2003 the dialogs will just look a little different.
    Post back if this doesn't solve the problem.
    BTW: If you add more rows to the header area the formula will need to be adjusted accordingly!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    RetiredGeek - that is just what I need. Your assistance is very much appreciated.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verda,

    You're welcome.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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