VBA Page Setup in Excel — Print Area, Fit to Page and Orientation

TL;DR — Worksheet.PageSetup defines the page: what is in it (PrintArea), which way it faces (Orientation), how it is scaled (Zoom or FitToPagesWide/FitToPagesTall), its margins, repeated title rows, and its header and footer. Both PrintOut and Save as PDF render these pages, so this is where layout problems get fixed. The rule that trips everyone: fit-to-page only works after you set .Zoom = False.

Sub SetUpReportPage()
    With Worksheets("Report").PageSetup
        .PrintArea = "$A$1:$H$60"
        .Orientation = xlLandscape
        .Zoom = False              ' required, or the two lines below are ignored
        .FitToPagesWide = 1
        .FitToPagesTall = False    ' as many pages tall as the data needs
        .PrintTitleRows = "$1:$2"
    End With
End Sub

This is the foundation of a three-part cluster on getting output out of Excel. PageSetup, covered here, decides what a page is. PrintOut sends those pages to a printer, and ExportAsFixedFormat sends the same pages to a PDF. The output commands are one line each; the page is where the real work is. When a printout or a PDF looks wrong, the fix is almost always here, not in the command that produced it.

What you'll learn

Why the page definition controls both paper and PDF
Setting and clearing the print area, including from a Range
Orientation, paper size and centring
Scaling: Zoom versus fit to pages, and the rule that links them
Repeating title rows, and headers and footers with page numbers
Margins in inches or centimetres
Making page setup fast with PrintCommunication

The mental model: the page is the product

When Excel prints or exports, it does not print a table — it cuts the sheet into pages and outputs those. PageSetup holds every rule for that cut: the area to include, the paper, the direction, the scale, the margins, and what repeats on each page. PrintOut and ExportAsFixedFormat are just two destinations for the result. So think of page setup as the product and the output command as delivery. A report macro that sets the page explicitly produces the same result on every machine; one that relies on whatever was left there last time does not.

The print area

PrintArea is a string holding an A1-style address, not a Range. To set it from a range, pass the range's Address:

Dim ws As Worksheet
Set ws = Worksheets("Report")
ws.PageSetup.PrintArea = ws.Range("A1").CurrentRegion.Address   ' the data block
ws.PageSetup.PrintArea = ""                                     ' clear it: print everything

Setting it from CurrentRegion or from the last row is how a report prints exactly its data, however many rows it has this month. Clearing it with an empty string is just as important: a print area left over from an old run silently cuts the next report short.

Orientation, paper size and centring

With ws.PageSetup
    .Orientation = xlLandscape         ' or xlPortrait
    .PaperSize = xlPaperA4             ' or xlPaperLetter
    .CenterHorizontally = True
End With

PaperSize depends on what the printer driver supports; a size the printer lacks raises an error. For a workbook that goes to both Europe and the US, set it on purpose rather than assuming.

Scaling: Zoom versus fit to pages

There are two ways to scale, and they are mutually exclusive. Zoom is a percentage from 10 to 400. FitToPagesWide and FitToPagesTall let Excel choose the scale to fit a page count. Excel only uses the fit settings when Zoom is False — while Zoom holds a number, they are quietly ignored. That is why so many "fit to one page" macros do nothing:

With ws.PageSetup
    .Zoom = False
    .FitToPagesWide = 1        ' all columns on one page across
    .FitToPagesTall = False    ' let the rows run onto as many pages as needed
End With

FitToPagesWide = 1 with FitToPagesTall = False is the setting most reports want: never split columns, let rows flow. Setting both to 1 squeezes everything onto a single page, which is fine for a summary and unreadable for a long list.

Title rows, headers and footers

PrintTitleRows repeats header rows at the top of every page; it takes a row address as a string. Headers and footers take text with codes: &P is the page number, &N the page count, &D the date, &A the sheet name.

With ws.PageSetup
    .PrintTitleRows = "$1:$2"
    .CenterFooter = "Page &P of &N"
    .RightHeader = "&A - printed &D"
End With

Because & starts a code, a literal ampersand in header text must be doubled: "R&&D Report" prints as R&D Report.

Margins in inches or centimetres

Margins are measured in points, not inches. Convert with Application.InchesToPoints or Application.CentimetersToPoints:

With ws.PageSetup
    .LeftMargin = Application.CentimetersToPoints(1.5)
    .RightMargin = Application.CentimetersToPoints(1.5)
    .TopMargin = Application.InchesToPoints(0.75)
End With

Writing .LeftMargin = 1 sets a margin of one point — about a third of a millimetre — which is a common reason a printout suddenly runs to the edge of the paper.

Making page setup fast with PrintCommunication

Every PageSetup assignment talks to the printer driver, so a block of ten settings can take seconds, and more in a loop over many sheets. Turn that conversation off while you set, and back on when you finish:

Application.PrintCommunication = False
With ws.PageSetup
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With
Application.PrintCommunication = True      ' applies the batched settings

Always switch it back on — the settings are only committed when it returns to True. The same thinking applies to ScreenUpdating: stop Excel doing work you do not need while the macro runs.

How ExcelMaster helps

Page setup is where report macros quietly fail: a Zoom that silently disables fit-to-page, a margin set in points instead of centimetres, a print area left from last month, a footer with an ampersand that vanishes. None of it raises an error — the report just comes out wrong, on paper or in a PDF.

ExcelMaster lets you describe the page — "landscape A4, one page wide, repeat the first two rows, page numbers in the footer" — and writes the complete PageSetup block with the zoom rule, unit conversion and fast mode handled, then previews the pages so you can see the layout before anything is printed or exported.

Frequently asked questions

How do I set the print area in VBA?

Assign an address string to PageSetup.PrintArea, such as Worksheets("Report").PageSetup.PrintArea = "$A$1:$H$60". To use a range, pass its Address: .PrintArea = Range("A1").CurrentRegion.Address. Set it to "" to clear the print area and print the whole sheet.

Why is FitToPagesWide not working?

Because Zoom still holds a percentage. Excel ignores FitToPagesWide and FitToPagesTall unless PageSetup.Zoom = False. Set Zoom to False first, then the fit settings, typically FitToPagesWide = 1 and FitToPagesTall = False.

How do I set landscape orientation in VBA?

Use Worksheets("Report").PageSetup.Orientation = xlLandscape. Use xlPortrait to switch back. The setting applies to both printing and PDF export, because both render the sheet's page setup.

How do I repeat header rows on every printed page?

Set PageSetup.PrintTitleRows to a row address string, for example "$1:$2" to repeat rows 1 and 2. For repeating columns on the left of every page, use PrintTitleColumns with a column address such as "$A:$A".

Why is my PageSetup macro so slow?

Each PageSetup property talks to the printer driver. Wrap the settings in Application.PrintCommunication = False and Application.PrintCommunication = True; Excel then applies them in one batch. Remember to set it back to True, or the settings are not committed.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-29.