Automatically Set Print Area

[cboyer21]

I have a worksheet that is several columns wide. I want to automatically set the print area for columns A:I and only as far down as visible data. I have formulas in the print area. Here is the code I have now and I don't know how to get it to only print the columns I want:

Sub Set_Print_Area3()


Dim x As Long, lastCell As Range, LR As Long

x = ActiveSheet.UsedRange.Columns.Count
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
LR = lastCell.Row
Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) <> 0
Set lastCell = lastCell.Offset(-1, 0)
LR = lastCell.Row
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 7
End With



ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub

Any help would be appreciated.

Thanks

 

[jc.021286]

As your data is in A:I (column I being 9)

would

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(65000,9).End(xlUp)).Address
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = 7
 End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub

Hope this does the trick,
jc

 

[cboyer21]

Thanks for the quick response. It is printing the correct columns, but is printing down to the last row that contains a formula.

 

[jc.021286]

Oh,
my apologies, I did not figure that that could be an issue.

Let me pose this question, what sets the equations to not be zero?
Could you apply a sort field on the columns A:I and tell it to ignore values of 0 in column I?

Is there another column that would be better suited to use this?

If you have:
Date - Product A qty - Product B qty - Product C qty - Formulas -->>>

then using the code on Column B or D would be better and then offset out to column I, which you know how to do as you have used in your first code.

Also, if the sections you do not want to include result in zero, perhaps using a countif for cells in the range that are equal to 0, and subtract that from your row, this should bring the print area back to the last valid data point.

Several ways, just depends on what suits your needs.
jc

 

[cboyer21]

Thanks for your help, I will play around with your suggestions.