Setting the Print Area with VBA
When you set the Print Area (File>PageSetup>Sheet Tab), Excel creates a Name called Print_Area. It doesn’t appear to be any more complicated than that because you can create that Name manually and get the same effect. That leaves two ways to set the Print Area in VBA, by accessing the PageSetup object and by accessing the Names collection object.
Sub SPAPageSetup()
Sheet1.PageSetup.PrintArea = “A1:F15″
End Sub
Sub SPAName()
Sheet1.Names.Add “Print_Area”, Sheet1.Range(”A1:F15″)
End Sub
To remove the Print Area, you again have two options. Using the PageSetup object, set the PrintArea property to an empty string. For Names, use the Delete method. If the Name doesn’t exist, VBA will raise an error, so it’s best to use some error checking for that method.
Sub DPAPageSetup()
Sheet1.PageSetup.PrintArea = “” ‘empty string
End Sub
Sub DPAName()
On Error Resume Next
Sheet1.Names(”Print_Area”).Delete
On Error GoTo 0
End Sub
What if the range changes depending on the amount of data transfered into the spreadsheet. I can count the # of columns and rows and set a variable represented by “Var = I9″ but I can’t find a way for the Range(”A1:Var”) to be accepted.
Try Range(”A1:” & Var).
I prefer Range(”A1″).Resize(, )
Seems my response was edited unintentionally:
I prefer Range(”A1?).Resize(NumberOfRows, NumberOfColumns)
make sure there is a printer installed when using the PrintArea procedure - otherwise it will return an error.
What If you would like to print 2 area’s
on 2 sheets
somehow i get an error message using
Sheet1.PageSetup.PrintArea = “A1:F15,A20:F45″
first range on page1
second range on page 2
Well the easiest way to do it is probably
sheet1.range(cells(1,1),cells(10,10)).printout
That will printout the range A1:J10
any of the numbers can be changed for variables.. so you can use that to print out multiple areas on the same sheet onto different pages
How do I set a specific print area using an assigned date? Example:
Row 8 contains date headers and I want the user to be able to print a date range.
In order to build a range you can also do the following
Dim strRange as string
strRange = “A1:N” & Var
This way you can determine what the number of rows are and set Var to that value.
I am trying to print one long line from a worksheet by breaking it up. A1:G1 on the first line with H1:N1 on the second line using Chr(10) or vbCrLf so that I can maintain the font size. Does anyone know what the syntax would be?
what if i want to set the print area according to what I have selected
Below is my code, I have made a selection of the cells I want to set and i want to set the print area
Range(”A1:C1″).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea =