How to Select the Print Area Using VBA in Excel

This is the sample dataset.

Enter the Code in Visual Basic Editor

Go to the Developer tab.
Click Visual Basic.

In the Visual Basic For Applications window, click Insert and select New Module.

Enter the code and press F5 to run it.

Method 1 – Set the Print Area based on a range Using VBA in Excel

1.1 Continuous Range

Selected Range:
B2:G5 is set as the print area.

Enter the following code and press F5 to run it.

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = Selection.Address
End With
End Sub

Click the namebox.

Click and select “Print_Area”. It displays the selected print area.

Print:
Add the Sheets.PrintOut method to the code:

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = Selection.Address
.PrintOut
End With
End Sub

Run the code.
Save the print area as a pdf.

1.2 Discontinuous Range

Selected Range:

The print area is the sales data for the product “Apple”.

Run the same code and follow the same process as in 1.1 to see the selected print area.

Method 2 – Use the Usedrange to Set the Print Area Using Excel VBA

By using the Worksheet.UsedRange property, you’ll select the used range on a specified worksheet (Sheet1, here) and set it as the print area.

Enter the following code and press F5 to run it.

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
End With
End Sub

Click the name box and select “Print_Area”. The whole used range in Sheet1 was selected.

Add the Sheets.PrintOut method in the above code to enable the print option.

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
.PrintOut
End With
End Sub

Method 3 – Running a VBA Code to Set the Print Area with a Pre-Defined Range in Excel

B2:G7 was set in the VBA code as the print area.

Enter the following code and press F5 to run it.

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = .Range("B2:G7").Address
End With
End Sub

Click name box and select “Print_Area”. The predefined range B2:G7 in Sheet1 was selected.

Add the Sheets.PrintOut method to the code to enable the print option.

Sub PrintAreaToSelection()
With Sheets("Sheet1")
.PageSetup.PrintArea = .Range("B2:G7").Address
.PrintOut
End With
End Sub

Read More: Excel VBA: Print Range of Cells

Things to Remember

You can also use the Sheets.PrintPreview method to enable the print option. To use the PrintPreview method, replace the .Printout with .PrintPreview in the code.

Download Practice Workbook

Download this practice workbook to exercise.

Set Print Area to Selection.xlsm

Get FREE Advanced Excel Exercises with Solutions!

How to Select the Print Area Using VBA in Excel – 3 Methods