To set an Excel worksheet’s print range with VBA, assign an address to its PageSetup.PrintArea property: Worksheets("Report").PageSetup.PrintArea = "$A$1:$F$40". Then print or preview the worksheet; to remove the saved range, set PrintArea to an empty string.
What a print range does in Excel
A print range, also called a print area, tells Excel which cells on a worksheet to include when that sheet is printed. It does not delete or hide cells, change what appears on screen, or define a range for formulas. It is a worksheet-level page setting, so setting a print area on one sheet does not set it on every sheet in the workbook.
In VBA, the setting belongs to the worksheet’s PageSetup object. The address is supplied as text, commonly in absolute A1 notation such as $A$1:$F$40. Absolute references make the intended boundaries explicit. Once a print area is set, Excel normally uses it when printing that worksheet, unless printing options or VBA arguments tell Excel to ignore print areas.
Setting a print area does not guarantee that the output fits neatly on one page. Page orientation, margins, scaling, paper size, and page breaks still affect the result. Think of the print area as the boundary of the content, and page setup as the instructions for laying that content onto paper or a PDF.
Set a fixed print area with VBA
This example sets a fixed area on a worksheet named Report. Change the sheet name and cell addresses to match your workbook. The sheet must exist in the workbook containing the VBA code.
Sub SetReportPrintArea()
Dim ws As Worksheet
' Use the worksheet that contains the report.
Set ws = ThisWorkbook.Worksheets("Report")
' Set the cells Excel should include when printing this sheet.
ws.PageSetup.PrintArea = "$A$1:$F$40"
End Sub
Run the macro from Excel’s desktop app after saving a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is useful before running code that changes workbook settings. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, paste the procedure, and run it with F5 while the cursor is inside it. You can also run a saved macro from Developer > Macros.
The code uses ThisWorkbook, meaning the workbook that contains the macro. That is often safer than ActiveWorkbook, which refers to whichever workbook currently has focus and could be a different file. If the macro is stored in Personal.xlsb or an add-in instead of the report workbook, adjust the workbook reference deliberately.
Build the print range from changing data
A fixed address works well for a report with a known layout. For a list that grows over time, calculate the last used row and build the range at run time. This example assumes headers are in row 1, the data spans columns A through F, and every record has a value in column A.
Sub SetGrowingReportPrintArea()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Report")
' Find the last nonempty cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Include the header and all rows through the last record.
ws.PageSetup.PrintArea = ws.Range("A1:F" & lastRow).Address
End Sub
The End(xlUp) method moves upward from the bottom of column A to the next nonempty cell, much like pressing Ctrl+Up from the bottom of a column. The resulting address is assigned to PrintArea; for example, if the final record is on row 125, the print area becomes $A$1:$F$125.
This method depends on the data layout. If column A can be blank on the final record, it may stop too early. Choose a column that is reliably populated, or use a more suitable way to identify the last row for the particular workbook. Also consider what should happen when the sheet contains only headers: the example will print row 1, which may be appropriate, but may not be the desired report behavior.
Preview or print the selected worksheet
Setting PrintArea only changes the worksheet’s print setting; it does not send anything to a printer. Preview first to check the page layout. The following procedure sets the same fixed area and opens Excel’s print preview:
Sub PreviewReport()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Report")
ws.PageSetup.PrintArea = "$A$1:$F$40"
' Show the output before sending it to a printer.
ws.PrintPreview
End Sub
To print without opening the preview, use ws.PrintOut after setting the area. For example, ws.PrintOut Copies:=1, IgnorePrintAreas:=False prints one copy while honoring the worksheet’s print area. Printing sends output to Excel’s current printer destination, which may be a physical printer or a PDF printer depending on the computer’s configuration. Use preview when you are unsure; a macro that calls PrintOut can produce paper immediately.
If you want to print a range for a single operation without saving a worksheet print area, you can print the range object directly. For instance, ws.Range("A1:F40").PrintPreview previews that range. This is useful when a recurring print area would be inconvenient, while PageSetup.PrintArea is better when the sheet should retain the same boundary for later printing.
Fit the print area to pages
A selected print range can span several pages. To make a wide report fit the width of one page while allowing it to continue down additional pages, set the worksheet’s scaling properties. Set Zoom to False so that Excel uses the fit-to-pages settings rather than a fixed zoom percentage.
Sub SetReportPageLayout()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Report")
ws.PageSetup.PrintArea = "$A$1:$F$40"
' Fit all columns to one page wide; allow multiple pages tall.
With ws.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 0
End With
End Sub
Landscape orientation can help when a report has many columns, but it is not automatically best for every sheet. Fitting a very wide table onto one page can make text too small to read. Preview the result and consider using more than one page wide or adjusting column widths and margins rather than forcing an unreadable scale. The available paper size and printer settings can also affect pagination.
For a manual equivalent in Excel for Microsoft 365 on Windows, use Page Layout > Print Area > Set Print Area to save the currently selected cells as the print area. Use Page Layout > Orientation to choose portrait or landscape. File > Print opens the print screen and preview. The VBA editor and macros are available in desktop Excel for Windows and Mac, but Excel for the web does not run VBA; use desktop Excel to run these procedures.
Change, clear, or use multiple print areas
Assigning a new address replaces the worksheet’s previous print area. For example, setting ws.PageSetup.PrintArea = "$A$1:$G$55" changes the boundary; it does not add the new cells to the old address. This makes a print area straightforward to update in a report-generation macro.
To remove the print area and return to printing the worksheet’s used content according to Excel’s other print settings, clear the property:
Sub ClearReportPrintArea()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Report")
' An empty string removes the saved print area.
ws.PageSetup.PrintArea = ""
End Sub
You can also clear it manually with Page Layout > Print Area > Clear Print Area. A common misunderstanding is that clearing the print area clears the cells themselves; it does not. The setting controls printing only.
Excel can store more than one print range on a worksheet by using a comma-separated address, such as $A$1:$C$20,$E$1:$G$20. Noncontiguous areas may be laid out as separate print areas, so preview them rather than assuming Excel will arrange them as a single continuous table. If the goal is one compact report, copying the desired fields into a contiguous report layout is often easier to control.
Common problems and how to diagnose them
- The macro reports a subscript error. Check the worksheet tab name, including spaces, and confirm that the code refers to the correct workbook.
Worksheets("Report")must match an existing sheet name. - Excel prints the wrong cells. Confirm the macro set the print area on the sheet being printed. Print areas belong to individual worksheets; setting one on
Reportdoes not affectSummary. - New records are missing. A fixed address does not expand automatically just because rows were added. Use a dynamic last-row calculation, or update the address when the data changes.
- The last rows are omitted by the dynamic example. Check whether the chosen anchor column is populated in every record. A blank in column A can make the last-row calculation stop above the true end of the table.
- The printout is too small or spans unexpected pages. Review orientation, scaling, paper size, margins, and page breaks in print preview. A print area defines what is included, not how large it appears.
- VBA prints more than the saved area. Check the print command’s options. In particular,
IgnorePrintAreas:=TruetellsPrintOutto ignore the saved print area; useFalsewhen you want it respected.
Hidden rows and columns, manual page breaks, and print titles can also influence what the printed pages look like. A print title repeats designated heading rows or columns on pages; it is a separate page setup feature, not a replacement for a print area. If a report looks different from the worksheet grid, inspect these settings as well as the selected boundary.
When to use a print area versus printing a range
Choose a saved print area when the same worksheet is regularly printed and its intended boundary should persist. This suits monthly reports, forms, and standard summaries. A macro can recalculate the boundary before printing, so the saved setting stays aligned with a changing table.
Choose a direct range print when a one-time macro should print just a particular block without changing the sheet’s stored print setup. This can be useful in a workbook where different users print different sections. If the output needs a specific orientation, scaling, headers, or multiple pages, set those page options too; a range by itself does not make every layout decision.
For repeated, polished output, a dedicated report sheet is often the simplest alternative to printing scattered areas from a data sheet. It lets you control column widths, headings, and page breaks without disturbing the source data. If several worksheets must be printed together, handle each worksheet’s print area and print call explicitly, and preview the combined job before sending it to a printer.
Frequently Asked Questions
How do I set the print area in Excel VBA?
Assign an A1-style address to the target worksheet’s PageSetup.PrintArea property, such as Worksheets("Report").PageSetup.PrintArea = "$A$1:$F$40". Make sure the worksheet reference names the intended sheet.
How do I clear a print area with VBA?
Set it to an empty string: ws.PageSetup.PrintArea = "". This removes the saved boundary but does not delete or change any worksheet cells.
Can VBA print a range without setting a print area?
Yes. Call a print method on a range, such as ws.Range("A1:F40").PrintOut, or use PrintPreview first. This is useful for a one-off range; a saved print area is more convenient for repeated worksheet printing.
Why does my print area stop before the last row?
If the macro finds the last row by looking up column A, a blank cell in column A may cause it to stop early. Use a column that is filled for every record or adapt the last-row logic to the structure of your data.
Can I run Excel VBA print macros in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; you can still use the web app’s available printing options without VBA.
Related
- Scatter Plot in Excel: Show Multiple Variables Clearly
- Scatter Plot Excel: Customize Axes and Scale
- Row Grouping in Excel: Collapse and Expand Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header…
- Keyboard Recorder Macro: How It Works and When to Use OneA keyboard recorder macro is a tool that captures a sequence of keystrokes—and sometimes mouse actions or delays—so…
- VBA Delete Dictionary Items, Keys, or the Whole ObjectIn VBA, remove one entry from a Scripting.Dictionary with dict.Remove key, clear every entry but keep the dictionary…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
- VBA Code to Insert an Image in an Excel Cell AutomaticallyUse VBA’s Shapes.AddPicture method to insert a picture, then position and size it to match a worksheet cell.…
- Input Box in Excel VBA: Use InputBox and Get DataIn Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.