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!
Variable Print Area
-
-
-
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
Thanks, this is a good start, now if someone could help getting the rows selected I would be all set!
-
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:
CodeDim 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$" & lngLastRowNote 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.
Code
Display More'You can skip the step of [b].Select[/b]ing things before using them 'Instead, you could use something like: ' Columns("B:C").EntireColumn.Hidden = True 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 'You can also set rows 5-6 to print at the top by using the [b].TitleRows[/b] property of the [b]PageSetup[/b] object. 'You'll need to use absolute ($) references for that too. ActiveSheet.PageSetup.PrintArea = "$A$5:$Q$" & lngLastRow 'Remember to print the sheet, not the selection Selection.PrintOut Copies:=1, Collate:=True Range("A7").Select ActiveSheet.Outline.ShowLevels RowLevels:=2 'Is this meant to be here, or down at the end of the code? Selection.PrintOut Copies:=1, Collate:=True ActiveSheet.Outline.ShowLevels RowLevels:=3 Range("A5:Q5").Select 'There shouldn't be any need to calculate this row again, as it doesn't change even if you show/hide rows lngLastRow = Columns("A:A").Find(What:="5701 Total", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'This shouldn't need to change; showing and hiding rows doesn't affect the row numbers ActiveSheet.PageSetup.PrintArea = "$A$5:$Q$" & lngLastRow Columns("A:F").Select Selection.EntireColumn.Hidden = False Range("A7").Select -
Re: Variable Print Area
:thanx:
Thanks to all who helped!! I got it figured out and I appreciate it!!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!