Table of Contents
VBA, or Visual Basic for Applications, is a programming language that can be used to automate tasks in Microsoft Excel. One useful function of VBA is the ability to set the print area in a spreadsheet. This allows users to specify which cells or ranges of cells should be printed when the document is sent to a printer.
To use VBA to set the print area in Excel, you will need to open the Visual Basic Editor by pressing Alt+F11. Then, you can use the “Activesheet.PrintArea” property to define the desired cells or ranges to be included in the print area. For example, to set the print area to include cells A1 to D10, you would use the following code:
Activesheet.PrintArea = “A1:D10”
Other methods for setting the print area in VBA include using the “PageSetup.PrintArea” property or the “PageSetup.SetPrintArea” method.
Some examples of when setting the print area with VBA may be useful include when creating reports or invoices with specific data that needs to be printed, or when creating a custom print layout for a spreadsheet with multiple tables or charts. By utilizing VBA to set the print area, you can easily automate this process and save time when printing your Excel documents.
Set Print Area Using VBA (With Examples)
You can use the following syntax in VBA to set the print area and display a print preview before actually printing a sheet:
Sub SetPrintArea()
With Sheets("Sheet1")
.PageSetup.PrintArea = Selection.Address
.PrintPreview
End WithEnd Sub
This particular macro will set the print area to be the currently selected cell range in the sheet called Sheet1 and then provide a print preview.
Note: If you want to print the selected range without previewing it, then replace .PrintPreview with .PrintOut in the macro.
The following example shows how to use this syntax in practice.
Example: How to Set Print Area Using VBA
Suppose we have the following sheet in Excel with some data about various basketball players:
We can create the following macro to specify a print area and display a print preview before actually printing a sheet:
Sub SetPrintArea()
With Sheets("Sheet1")
.PageSetup.PrintArea = Selection.Address
.PrintPreview
End WithEnd Sub
Suppose we then select the cell range A2:B7:
When we run this macro, the print area is automatically set to this selected cell range and the following print preview window appears:
This shows us exactly what the page will look like if we print the currently selected range of cells.
If we change the selected range of cells, then the print area will automatically change.
For example, suppose we instead select the range A1:B11:
If we run this macro again, the print area is automatically set to this selected cell range and the following print preview window appears:
The print preview now shows that we will print the range A1:B11 if we proceed with printing.
Note: You can find the complete documentation for the PrintArea property in VBA .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: