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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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: Excel VBA: Set Print Area for Multiple Ranges


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

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: Print Range of Cells


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.


Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo