Excel VBA set print area

This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA.

Excel VBA set print area

To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area

To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property.

For example,  in order to set Print Area A1:N21, we can write

Public Sub printArea()
    ActiveSheet.PageSetup.printArea = "$A$1:$N$21"
End Sub

Excel VBA set page width and height

After we have defined the Print Area, the next step is to set the page width and height.

For most of the time, we want the page Width to be 1 page, while the height is Automatic, meaning we let Excel decide how many pages to print depending on how many rows are in the data, we just need to ensure all the field headers are displayed in one page.

Public Sub printArea()
    With ActiveSheet.PageSetup
        .printArea = "$A$1:$N$21"
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
End Sub

While the above code would work perfectly, in fact, it is more correct to set the FitToPagesTall = 0 to make it Automatic, but this command would generate an error

Run-time error ‘1004’: Unable to set the FitToPagesTall property of the PageSetup class

The error is generated when we have not turned on the communication to the printer. To avoid this error, we can temporarily turn off the communication using Application.PrintCommunication Property.

Public Sub printArea()
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .printArea = "$A$1:$N$21"
        .FitToPagesWide = 1
        .FitToPagesTall = 0
    End With
    Application.PrintCommunication = True
End Sub

Excel VBA set Print Title

In case the number of records (rows) is so large that it takes more than 1 page to print all the data, we need to ensure all pages contain the field headers on top of each page. In Excel spreadsheet, we set it under Page Layout > Print Titles > Rows to repeat at top

In VBA, we set this using PrintTitleRows Property.

Public Sub printArea()
    With ActiveSheet.PageSetup
        .printArea = "$A$1:$N$21"
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintTitleRows = "$1:$1"
    End With
End Sub

Excel VBA set Orientation

Sometimes we want to rotate the paper by 90 degree to Landscape when there are too many fields to display in Portrait layout.

In VBA, we can either set Orientation = xlPortrait or Orientation = xlLandscape

Public Sub printArea()
    With ActiveSheet.PageSetup
        .printArea = "$A$1:$N$21"
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintTitleRows = "$1:$1"
        .Orientation = xlLandscape
    End With
End Sub

Other Properties

I have introduced the most commonly used properties in this article, in fact, there are more than 50 properties for PageSetup Object. If you want to learn other properties, click here.