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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,774
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top