Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Print Area (Excel 97-SR2)

    Is there any way to dynamically recalculate the Print Area and not have Excel interfere with it?

    I have a named cell 'CalcPrintArea' with the formula:

    ="$A$1:" & ADDRESS(COUNT($A$7:$A$2009)+7,8)

    In Inser Names, I have defined 'Print Area' as:

    =INDIRECT(CalcPrintArea)

    This works fine, so long as you/the user does not go into PAge Setup. Why?

  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 Area (Excel 97-SR2)

    Going into PageSetup, gets excel to evaluate the printarea. Once it evaluates the area it changes it to what the INDIRECT reads rather than the function that you entered (it actually changes the named range "print_area" from "indirect(calcprintarea)" to its value.

    You could reset it in the Beforeprint event if desired.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Print Area (Excel 97-SR2)

    Harumph! OK.. Plan B.. Work out how to move the worksheet in question out to another worksheet that only moves the cellsthat actually have some data in it..

    OK.. The particular sledgehammer I have used to crack this nut.. Basically I'm importing data from MS_Project, re-formatting it and doing some smart stuff with confitional formatting to highlight overdue and nearly due items.

    I have two worksheets, Project_Data and Project_Format. The first is where the data is imported. The second is the one that has the smarts regarding formatting. To allow for Project files of varying sizes the second worksheet allows for up to 2,000 import rows. Often there are less than 500 rows, simple approach would be to reduce the 2,000 rows.

    The trick with the INDIRECT(CalcPrintArea) was to stop printing 2,000 rows. Plan B is to export the 'Active' rows to another worksheet, in fact it may as well be a separate spreadsheet. I ass-u-me this will be a job for VBA? Any pointers? As an aside, through the CalcPrintArea cell I know that I need to export all the cells in the area $A$1:$H$259, for the currently imported MS Project data.

  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 Area (Excel 97-SR2)

    How about in your "smart" worksheet, add a column which determines whether that row needs to be printed (check for blanks, nullstrings, whatever). Do an Autofilter on this column (manual or macro before print?) to "hide" those rows. Then you don't need to set the print area,letting excel figure it out automatically should work fine.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Print Area (Excel 97-SR2)

    The VBA code below, placed in the Workbook Before Print event routine, will change the PrintArea for Sheet1 to A1 through column I and whatever row has the last data in column A every time the sheet is printed.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim lLastRow As Long
    If ActiveSheet.Name <> "Sheet1" Then Exit Sub
    lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 2
    With ActiveSheet.PageSetup
    .PrintArea = "A1:" & ActiveSheet.Range("A1").Offset(lLastRow, 8).Address
    End With
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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