The article will show you some methods on how to print a range of cells in Excel by using Microsoft Visual Basic for Application (VBA). If you have a dataset that contains lots of information but you don’t want to print all of it, rather you want partial information of that dataset, you can use VBA effectively in that case. In the following dataset, we have information about some people.
How to Print Range of Cells by Excel VBA: 5 Ways
1. Using Excel VBA PrintOut Method to Print Range of Cells
The simplest way to print a range of cells in VBA is to define the range in it using the PrintOut Method of VBA. Suppose you want to print only the Name and Age of these people. Let’s discuss the procedure below.
- First, open Visual Basic from the Developer Tab.
- A new window will appear. Select Insert >> Module.
- Now type the following code in the VBA Module.
Sub PrintCriteria()
Range("B4:C10").PrintOut
End Sub
Here, we define the range B4:C10 to print by the Range Method and PrintOut Method. We want to see Names and Ages only, so we chose the range B4:C10.
- Go back to your sheet and run the Macro named PrintCriteria as it is the name of our current Macro.
It was supposed to Print the selected range but right now I’m going to show you how you can Save the file for further use or to Print it later.
- The Save window will appear. The range you chose, it will be saved as PDF if you choose PDF and in the given location of your drive. Give the PDF file a name. In this case, I named my file PrintOut Method.
- Click Save.
- After that, you can find the file in that location. Open it and you will see the range you have chosen.
You can print this range of cells if you want by pressing CTRL + P and giving necessary commands.
Read More: Excel VBA: Print Preview for Selected Range
2. Defining the Excel Sheet to Print Range of Cells by Excel VBA
We can also define the sheet name in VBA and use the PrintOut Method of VBA to print a range of cells. Suppose you want to print only the Name and Age of these people. Let’s discuss the procedure below.
Steps:
- Type the following code in the VBA Module. (To see how to open VBA Module, please go to Section 1)
Sub DefiningSheet()
Sheets("Defining_Sheet").Range("B4:C10").PrintOut
End Sub
Here, we define our Excel sheet in the VBA Module. We select the range from the sheet named Defining_Sheet. We want to see names and ages only, so we chose the range B4:C10.
- Go back to your sheet and run the Macro named DefiningSheet as it is the name of our current Macro.
It was supposed to Print the selected range but right now I’m going to show you how you can Save the file for further use or to Print later.
- The Save window will appear. The range you chose, it will be saved as PDF if you choose PDF and in the given location of your drive. Give the PDF file a name. In this case, I named my file Defining Sheet.
- Click Save.
- After that, you can find the file in that location. Open it and you will see the range you have chosen.
You can print this range of cells if you want by pressing CTRL + P and giving the necessary commands.
Read More: How to Print Specific Sheets Using VBA Macro in Excel
3. Selecting Range of Cells to Print by Excel VBA
Another way to print a range of cells by VBA is to select the range that we prefer to print. Suppose you want to print only the Name, Age, and Occupation of these people. Let’s discuss the procedure below.
Steps:
- Type the following code in the VBA Module. (To see how to open VBA Module, please go to Section 1)
Sub SelectionToPrint()
Selection.PrintOut
End Sub
Here we use the Selection Property in the PrintOut Method to print the selected range of cells from an Excel sheet.
- Go back to your sheet and select the cells B4:D10.
- Run the Macro named SelectionToPrint as it is the name of our current Macro.
It was supposed to Print the selected range but right now I’m going to show you how you can Save the file for further use or to Print it later.
- The Save window will appear. The range you chose, it will be saved as PDF if you choose PDF and in the given location of your drive. Give the PDF file a name. In this case, I named my file Printing by Selection.
- Click Save.
- After that, you can find the file in that location. Open it and you will see the range you have chosen.
You can print this range of cells if you want by pressing CTRL + P and giving the necessary commands.
Read More: How to Set Print Area to Selection Using VBA in Excel
4. Define Range of Cells in the VBA Code to Print in Excel
We can also print a range of cells by VBA using the Range Method. Suppose you want to print the information about the first four persons. Let’s go through the procedure below.
Steps:
- Type the following code in the VBA Module. (To see how to open VBA Module, please go to Section 1)
Sub PrintRangeMethod()
Sheets("Range_Method").Select
Range("B4:E8").Select
ActiveSheet.PageSetup.PrintArea = "B4:E8"
ActiveWindow.SelectedSheets.PrintOut From:=1, _
To:=1, Copies:=1, Collate:=True
End Sub
Here, we want to print the range B4:E8 so we define it by the Range Method. Then we set it as PrintArea. And we used the PrintOut Method to print our desired range of cells.
- Go back to your sheet and Run the Macro named PrintRangeMethod as it is the name of our current Macro.
It was supposed to Print the selected range but right now I’m going to show you how you can Save the file for further use or to Print it later.
- The Save window will appear. The range you chose, it will be saved as PDF if you choose PDF and in the given location of your drive. Give the PDF file a name. In this case, I named my file Print by Range.
- Click Save.
- After that, you can find the file in that location. Open it and you will see the range you have chosen.
You can print this range of cells if you want by pressing CTRL + P and giving necessary commands.
5. Printing Range of Cells by Excel VBA With Statement
VBA With Statement can play a vital role to print a range of cells. Suppose you want to print the Names, Ages, and Occupations of the first four persons. Let’s go through the description below for that purpose.
Steps:
- Type the following code in the VBA Module. (To see how to open VBA Module, please go to Section 1)
Sub PrintWithStatement()
With Sheets("With_Statement")
.PageSetup.PrintArea = "B4:D8"
.PrintOut
End With
End Sub
Here, we applied the With Statement to define our Excel sheet which we named as With_Statement. Then we used PrintArea property to print our desired range of cells.
- Go back to your sheet and run the Macro named PrintWithStatement as it is the name of our current Macro.
It was supposed to Print the selected range but right now I’m going to show you how you can Save the file for further use or to Print it later.
- The Save window will appear. The range you chose, it will be saved as PDF if you choose PDF and in the given location of your drive. Give the PDF file a name. In this case, I named my file Print by Statement.
- Click Save.
- After that, you can find the file in that location. Open it and you will see the range you have chosen.
You can print this range of cells if you want by pressing CTRL + P and giving necessary commands.
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
The focus of this article is to help you understand some basic methods on how to print a range of cells by Excel VBA. These methods are pretty easy to understand. If you have your own ideas or feedback, please leave them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- How to Display Print Preview with Excel VBA
- Excel VBA Debug Print: How to Do It?
- Excel VBA: How to Set Print Area Dynamically
- Excel VBA: Set Print Area for Multiple Ranges
- VBA Code for Print Button in Excel
- Excel VBA to Display Print Preview for Multiple Sheets
