Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PrintArea Problems (Excel 2000)

    Hi all. Hope someone can help me with this one as I have very little hair left now...

    I need to set a dynamic print area depending on how many rows have data in them. I'm currently using this line of code

    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(count, 13))

    where 'count' is calculated earlier and is the number of rows that need to be printed. I can't see where I'm going wrong. Any help would be much appreciated. TIA
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PrintArea Problems (Excel 2000)

    Could you show us the line that sets the value of count?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PrintArea Problems (Excel 2000)

    Create a dynamic range name, and then set the range name as the print area or in the code.

    eg, =OFFSET$A$1,0,0,COUNTA($A:$A),10) calling it PrintRange
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PrintArea Problems (Excel 2000)

    How about:

    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(count, 13)).address

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PrintArea Problems (Excel 2000)

    Try this after you have created the dynamic range I mentioned in <post#=478248>post 478248</post#>

    <pre>Option Explicit
    Sub SetDynamicPA()
    Dim PA As Range

    Application.ScreenUpdating = False
    Application.Goto Reference:="DR" '(The name of the Dynamic Range Name)
    Set PA = Selection
    ActiveSheet.PageSetup.PrintArea = PA
    PA.Cells(1).Select
    Application.ScreenUpdating = True

    End Sub
    </pre>


    Cheers
    Regards,
    Rudi

  6. #6
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PrintArea Problems (Excel 2000)

    Thanks one and all. I've just got into work so haven't had chance to try any of your suggestions yet. When I do I'll let you know how I got on.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PrintArea Problems (Excel 2000)

    Hi Lyra,
    Have you tried creating a dynamic range called 'Sheetname'!Print_Area (replace with the name of your worksheet as appropriate). This should automatically update the print range as long as you don't press the clear print area button. (at least it works for me in Excel2002)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PrintArea Problems (Excel 2000)

    This is great Rory. In my last two posts, I created the same thing MANUALLY, using a Dynamic Range Name and a macro. Yours does it ALL automatically and in one simple step!!!

    Cool Tip..Lyra should enjoy this one!!!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PrintArea Problems (Excel 2000)

    I recommend creating another identical one with a "similar name" (perhaps "Print_AreaBckUp").

    The Print_Area name is one excel uses: clearing print area or changing it in page setup will affect this name. Having a separate one with a different name will keep a "backup". If the print_area gets changed, just go to insert - name - define, select "Print_AreaBckUp" and edit the name to remove the "BckUp" and you will "restore" the dynamic range without having to regenerate the OFFSET formula

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PrintArea Problems (Excel 2000)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PrintArea Problems (Excel 2000)

    <hr>just go to insert - name - define, select "Print_AreaBckUp" and edit the name to remove the "BckUp" and you will "restore" the dynamic range without having to regenerate the OFFSET formula<hr>
    Or alternatively, change the Sheet1!PrintArea to point to the "backup" name directly:
    =Print_AreaBckUp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PrintArea Problems (Excel 2000)

    A good alternative. It all depends on whether you want to deal with names or the page setup.

    Either method can lose the printarea in a variety of ways, but both have backup names so changing is not much a problem...

    Steve

Posting Permissions

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