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

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.

excel vba print range of cells


Download Practice Workbook


5 Ways to Print Range of Cells by Excel VBA

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.

Steps:

  • First, open Visual Basic from the Developer Tab.

  • A new window will appear. Select Insert >> Module.

excel vba print range of cells

  • 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.

excel vba print range of cells

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.

excel vba print range of cells

You can print this range of cells if you want by pressing CTRL + P and giving necessary commands.

Read More: How to Adjust Print Settings in Excel (8 Suitable Tricks)


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.

excel vba print range of cells

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.

excel vba print range of cells

You can print this range of cells if you want by pressing CTRL + P and giving the necessary commands.

Read More: How to Print All Sheets in Excel (3 Methods)


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.

excel vba print range of cells

  • 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.

excel vba print range of cells

  • 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 Selected Area in Excel (2 Examples)


Similar Readings:


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

excel vba print range of cells

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.

excel vba print range of cells

  • 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: How to Print Selected Cells in Excel (2 Easy Ways)


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

excel vba print range of cells

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.

excel vba print range of cells

  • 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: How to Set Print Area in Excel (5 Methods)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

excel vba print range of cells


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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo