How to Set Print Area to Selection Using VBA in Excel (3 Methods)

This article illustrates 3 different methods to select and set the print area of a dataset to selection using VBA code in Excel. We’ll also modify our code to enable the print option for the selected print area conveniently.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Set Print Area to Selection Using VBA Code in Excel

Let’s say we have a dataset to work with. Using different methods we’ll set different portions of the dataset as the print area for selection. The dataset is here below.

Excel VBA Set Print Area to Selection

Write Code in Visual Basic Editor

To set the print area for selection, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

  • Go to the Developer tab from the Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.


1. Set Print Area to Selection with Current Region Using VBA in Excel

In this example, we’ll set the print area for selection based on the current region i.e., the active selected range of the dataset.

1.1 Continuous Range

Selected Range:
We selected range B2:G5 to set the print area for selection.

Excel VBA Set Print Area to Selection

VBA Code to Set Print Area to Selection:
Copy and paste the following code and press F5 to run it.

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

Check the Selected Print Area:
Click the namebox, there is a named range as “Print_Area”.

Excel VBA Set Print Area to Selection

Now, click and select the “Print_Areanamed range. It’ll show the selected print area.

Excel VBA Set Print Area to Selection

Print:
To print the selected print area, we need to add the Sheets.PrintOut method in our code. The code is-

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

If we run the code, it’ll open up a window to save the print area as a pdf in our computer’s storage. Save the file in a preferable place.


1.2 Discontinuous Range

Similar to section 1.1, we can also set a discontinuous range as the print area for selection.

Selected Range:
We selected the sale data only for the product “Apple” to set the print area for selection i.e., row numbers 2, 3, 8, 10, and 11 in the following screenshot.

Excel VBA Set Print Area to Selection

Check the Selected Print Area:
Run the same code and follow the same process as section 1.1 to see the selected print area.

Excel VBA Set Print Area to Selection

Read More: How to Print Selected Area in Excel (2 Examples)


2. Use of Usedrange to Set Print Area to Selection Using Excel VBA

Selected Range:
By using the Worksheet.UsedRange property, we’ll select the used range on a specified worksheet (Sheet1, in this example) and set it as the print area for selection.

VBA Code to Set Print Area to Selection:
Copy and paste the following code and press F5 to run it.

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

Check the Selected Print Area:
Click the name box and then select the named range “Print_Area”. It selected the whole used range in Sheet1.

Excel VBA Set Print Area to Selection

Print:
We need to 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

Read More: Excel VBA: Print Range of Cells (5 Easy Methods)


Similar Readings


3. Run a VBA Code to Set Print Area to Selection with a Pre-Defined Range in Excel  

In this illustration, we’ll see how to define a range hardcoded in the VBA code to set the print area for selection.

Selected Range:
We defined the range B2:G7 in our VBA code to set the print area for selection.

VBA Code to Set Print Area to Selection:
Copy and paste the following code and press F5 to run it.

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

Check the Selected Print Area:
Click the name box and then select the named range “Print_Area”. It selected the predefined range B2:G7 in Sheet1.

Excel VBA Set Print Area to Selection

Print:
We need to add the Sheets.PrintOut method in the above 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: Set Print Area for Multiple Ranges (5 Examples)


Things to Remember

We could also use the Sheets.PrintPreview method instead of the Sheets.PrintOut method to enable the print option in our code. To use the PrintPreview method, we just need to replace the .Printout with .PrintPreview in the code. This will facilitate us with the preview layout of the print area before printing.


Conclusion

Now, we know how to set the print area for selection using Excel’s built functions with examples.  Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo