Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Print Range (Excel 2002 - XP SP2)

    Is there a way to create a dynamic named print range? The named print range starts at D7 and expands both down and across (both unknown quantities-rows and columns). Rows 1 through 6 will be repeated and is contained in the File, Page Setup, Sheet, Rows To Repeat dialog box. What I need is to name this range and continue to expand across columns and down the rows instead of always setting the print area. I can name a range larger than what will be printed but this causes blank pages to be printed and/or the need to view with the Page Break Preview then to adjust the printed pages.

    Can this be done?

    Jess

  2. #2
    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: Dynamic Print Range (Excel 2002 - XP SP2)

    You could add this to the thisworkbook object in the file.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.PrintTitleRows = "$1:$6"
    ActiveSheet.PageSetup.PrintArea = Range("d7").CurrentRegion.Address
    End Sub</pre>


    Before you print any worksheet in the file, the title rows will be set and the "current region" starting at D7 will be set as the print range.

    Per the HELP:
    "The current region is a range bounded by any combination of blank rows and blank columns."

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Location
    California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Print Range (Excel 2002 - XP SP2)

    How does one create a workbook object and in looking in Help, where is "Current Region" so I can better understand.

    thanks
    Jess

  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: Dynamic Print Range (Excel 2002 - XP SP2)

    open VB (alt-f11)
    In explorer pane find the VBAProject with your filename.
    In it you will see objects for each worksheet and one for thisworkbook (they look like FOLDERS)
    Dbl-click on the the thisworkbook and the macro pane will open on the right side
    Copy the code from the other post into this pane

    While in VB you can look at currentregion property by looking in HELP (it is in VB, not excel help)
    close VB (alt-Q)

    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
  •