Automatically Set Print Area

cboyer21

New Member
Joined
Dec 21, 2004
Messages
28
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jc.021286

Well-known Member
Joined
Apr 12, 2010
Messages
725
As your data is in A:I (column I being 9)

would
Code:
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

New Member
Joined
Dec 21, 2004
Messages
28
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

Well-known Member
Joined
Apr 12, 2010
Messages
725
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,718
Messages
5,660,482
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top