Set Print Area using VBA

[sjha]

This is what I have so far and this works fine. I have pre-defined the range but is there a way to print only up to the last row where there is data otherwise no need to print white spaces. Thanks for your time and effort. My code is below:

Sub Set_Print_Area()

Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-24
Range("A1:Q200").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$200" With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[VoG]

Untested

ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

 

[sjha]

That did not change to what I had. Also, what do I need to add/change so that there is no Page Breaks. In my current situation, in first page, it has just 6 lines of data then next page has about half and then full pages of data....then next 9 pages with empty data. What should I do here? Many Thanks!!

 

[VoG]

Try this http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=41&utilities=Sheets then run the code again.

 

[sjha]

Any other solution w/o installing third party utilities? Thanks!!

 

[VoG]

Try this on a copy of your sheet. Select the last cell with data then run this

Sub makelastcell()
Dim x As Integer
Dim str As String
Dim xlong As Long, clong As Long, rlong As Long
On Error GoTo 0
str = ActiveCell.Address
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
xlong = ActiveSheet.UsedRange.Rows.Count
xlong = ActiveSheet.UsedRange.Columns.Count
Range(Cells(1, ActiveCell.Column + 1), Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
Beep
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
ActiveWorkbook.Save
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
MsgBox "Sorry, Have failed to make " & str & " your last cell"
End Sub

 

[tutz]

hi
is it possible to create a code that allows to copy the print area and the print title to a new workbook?i've one code that copies the print area to a new workbook, for each worksheet of the principal workbook but can't insert in there the print title.
i would appreciate the help

cheers

 

[lenze]

Tutz, what are you really trying(Needing) to do!! Do you have formulas on your sheet that return "" so Excel is printing more than you need??

lenze

 

[tutz]

Let's imagine that I've a workbook with 2 worksheets - sheet1 and sheet2. I want to create a new workbook for each worksheet(sheet1 and sheet2) but only with the print area and the print title included in each one. It's like copy the sheet to a new workbook but only including the print area and the print title. all the rest is out.