Variable Print Area

  • I have a subtotaled spreadsheet. I want the print area to always start on cell C5 and always go over to column Q. I need to search for the last subtotal on the spreadsheet (which will be called "5701 Total") and then set the print area to cover column C to Q and all rows down to this last subtotal. The amount of rows included in this data can change. Thanks in advance for the help!

  • Re: Variable Print Area


    Not sure how to search for the row of the last subtotal, but I know you need to set a range. In the example below you go to the bottom of all rows and select that range.


    [vba]
    Range("C5:Q5").Select
    Range(Selection, Selection.End(xlDown)).Name = "PrintRangeName"


    ActiveSheet.PageSetup.PrintArea = "$C$5:" & "PrintRangeName"
    [/vba]

  • Re: Variable Print Area


    There are many ways to find the row you're looking for. Here's one. Assuming the "5701 Total" label is located in column A:


    Code
    Dim lngLastRow As Long
    
    
       'Find last row
       lngLastRow = Columns("A:A").Find(What:="5701 Total", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    
       'Set print area
       ActiveSheet.PageSetup.PrintArea = "$C$5:$Q$" & lngLastRow


    Note that the print area must be set with an absolute ($) range.

  • Re: Variable Print Area


    Ok - I'm obviously doing something wrong.
    I want to hide columns B & C (this works)
    Then select the print area starting at A5 over to column Q
    Going down to the last row which contains the "5701 Total" in column A
    Set the print area and print the entire spreadsheet
    Reduce the spreadsheet so only the subtotals show and then print again.
    I want Row 5 & 6 to print at the top of each page, which I set in page setup.
    Here's my code, but all I get is the first header row.
    Thanks again for the help!!


    Columns("B:C").Select
    Selection.EntireColumn.Hidden = True
    Range("A5:Q5").Select
    lngLastRow = Columns("A:A").Find(What:="5701 Total", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    ActiveSheet.PageSetup.PrintArea = "$A$5:$Q$" & lngLastRow
    Selection.PrintOut Copies:=1, Collate:=True
    Range("A7").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Range("A5:Q5").Select
    lngLastRow = Columns("A:A").Find(What:="5701 Total", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    ActiveSheet.PageSetup.PrintArea = "$A$5:$Q$" & lngLastRow
    Columns("A:F").Select
    Selection.EntireColumn.Hidden = False
    Range("A7").Select

  • Re: Variable Print Area


    The problem was, you were .Selecting Range("A5:Q5") in line 3, then printing out the selection later on in the code. You want to print the sheet, not the selection. I've added some comments to your code to help you out.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!