Excel VBA: Set Print Area for Multiple Ranges (5 Examples)

In this article, you will get to know 5 examples related to set print area for multiple ranges using VBA in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


5 Examples to Set Print Area for Multiple Ranges Using Excel VBA

1. Print Multiple Ranges from Active Sheet On Same Page

This method can print multiple ranges from the active worksheet on the same page. It merges those ranges column-wise.

So the very first column of the second range will be adjacent to the last column of the first range.

From the following dataset of the attached picture, we will pick up B4:C10 as the first range and C4:D10 will be our second range.

So in the output, we will get the same data table like the following one.

Now follow the steps below:

❶ Press ALT + F11 to open the VBA Editor.

❷ Then go to Insert  >> Module to create a new module.

Insert a New Module to Print Multiple Ranges from Active Sheet On Same Page

Insert the following code in the VBA Editor and Save it.

Sub Print_Multiple_Ranges_Active_Sheet()

Dim yRng1 As Range
Dim yRng2 As Range
Dim rxUni As Range

Set yRng1 = Sheets("Sheet1").Range("B4:C10")
Set yRng2 = Sheets("Sheet1").Range("C4:E10")

With ActiveSheet.PageSetup
        .printArea = yRng1.Address & "," & yRng2.Address
         ActiveWindow.SelectedSheets.PrintOut Preview:=Preview
End With

End Sub

Print Multiple Ranges from Active Sheet On Same Page Using Excel VBA

Breakdown of the Code

  • Here, I declared 3 variables
  • Then I input two ranges.
  • After that, I set PrintArea for the multiple ranges and then printed out the specified range using the With statement property.

❹ Press the F5 key to Run the code.

The printing will automatically begin. You will see the following table in the printed output.

Output: Print Multiple Ranges from Active Sheet On Same Page

Read More: How to Show Print Area in Excel (4 Easy Methods)


2. Set Print Area for Multiple Ranges of a Selected Sheet

You can use this method to set multiple ranges as print areas. You can also specify a sheet’s serial number to print a specific sheet.

To get the VBA code usage steps, Click_Here.

Insert the following VBA code in the VBA Editor and Save it.

Sub Print_Multiple_Ranges_Selected_Sheet()

    Worksheets(2).Range("B4:C10, B4:D10").PrintOut

End Sub

Set Print Area for Multiple Ranges of a Selected Sheet Using Excel VBA

Breakdown of the Code

  • Here, I’ve specified the worksheet number using the Worksheets()
  • Then I used the Range property to specify my ranges.
  • Finally, used the PrintOut command to start printing.

❷  Now press the F5 key to Run the code.

The printing will automatically begin.

And you will see the contents of the inserted ranges on separates pages like this:

Read More: How to Set Print Area for Multiple Pages in Excel (2 Ways)


3. Print Multiple Ranges on a Single Page by Merging Rows

You can use this method to set multiple ranges as print areas and print them all on the same page.

To get the VBA code usage steps, Click_Here.

Insert the following VBA code in the VBA Editor and Save it.

Sub Print_Multiple_Range_in_One_Page()

Dim yRng1 As Range
Dim yRng2 As Range
Dim yNewWs As Worksheet
Dim yWs As Worksheet
Dim yIndex As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set yWs = ActiveSheet
Set yNewWs = Worksheets.Add
yWs.Select
yIndex = 1

For Each yRng2 In Selection.Areas
    yRng2.Copy
    Set yRng1 = yNewWs.Cells(yIndex, 1)
    yRng1.PasteSpecial xlPasteValues
    yRng1.PasteSpecial xlPasteFormats
    yIndex = yIndex + yRng2.Rows.Coun
Next

yNewWs.Columns.AutoFit
yNewWs.PrintOut
yNewWs.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Print Multiple Ranges on a Single Page by Merging Rows Together Using Excel VBA

Breakdown of the Code

  • First, I declared 5 variables.
  • Then I used the Add property to open a new worksheet.
  • I used a For loop to Copy each of the selected areas and Paste their values as well as formats to the new worksheet sequentially.
  • After that, I used the AutoFit property to fit the pasted values perfectly.
  • I used the PrintOut property to print the contents of the newly added worksheet.
  • Finally, I used the Delete property to delete the worksheet.

❷ Go back to your worksheet and select the range that you want to print out.

❸ After that, press ALT + F8 to open the Macro dialog box.

❹ Select the macro Print_Multiple_Range_in_One_Page and hit the Run button.

Macro: Print Multiple Ranges on a Single Page by Merging Rows Together Using Excel VBA

The printing will automatically begin. You will get all your selected ranges printed on the same page like this:

Read More: How to Print Selected Area in Excel on One Page (3 Methods)


Similar Readings


4. Set Multiple Ranges From Multiple Sheets as Print Area

To set multiple ranges as the print areas from multiple sheets, you can follow this method.

For getting the VBA code usage steps, Click_Here.

Insert the following VBA code in the VBA Editor and Save it.

Sub Print_Multiple_Ranges_from_Multiple_Sheets()

Dim xWs As Worksheet

For Each xWs In Worksheets(Array("Sheet1", "Sheet2"))
With xWs
With .PageSetup
.printArea = "$B$4:$C$10,$B$4:$D$10"
.FitToPagesTall = 2
.FitToPagesWide = 1
End With
.PrintOut Copies:=1
End With
Next

End Sub

Set Multiple Ranges From Multiple Sheets as Print Area Using Excel VBA

Breakdown of the Code

  • Here, I declared a variable first.
  • Then I used a For loop to retrieve each sheet.
  • I also used the printArea property to set multiple ranges using the With statement.
  • Finally, I defined the print copy as 1 using the PrintOut Copies

❷  Now press the F5 key to Run the code.

The printing will automatically begin.

And you will see the contents of the inserted ranges on separates pages like this:

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


5. Print Multiple Named Ranges on a Single Page by Merging Columns

If you have too many ranges to print out, you can name each of them uniquely for convenience.

In this method, you will learn to print multiple named ranges on the same page by merging columns together.

From the following dataset of the attached picture, we will pick up B4:C10 as the first range and C4:D10 will be our second range.

So in the output, we will get the same data table as the following one.

To get the VBA code usage steps, Click_Here.

Insert the following VBA code in the VBA Editor and Save it.

Sub Print_Multiple_Named_Range()

Dim xRng As Range
Dim yRng As Range

Set xRng = Range("$B$4:$C$10")
Set yRng = Range("$D$4:$E$10")

With ActiveSheet
.PageSetup.printArea = Union(xRng, yRng).Address
ActiveWindow.SelectedSheets.PrintOut Preview:=Preview
End With

End Sub

Print Multiple Named Ranges on a Single Page by Merging Columns Together Using Excel VBA

Breakdown of the Code

  • First, I declared two variables.
  • Then I assigned cell ranges to different names.
  • After that, I used the Union function to merge those ranges column-wise to set the PrintArea .
  • Finally, I used the PrintOut property to print out the specified ranges.

❷ Now, go back to your worksheet first.

❸ After that, press ALT + F8 to open the Macro dialog box.

❹ Select the macro Print_Multiple_Named_Range and hit the Run button.

The printing will automatically begin. You will get all your selected ranges printed on the same page like this:

Output: Print Multiple Named Ranges on a Single Page by Merging Columns Together Using Excel VBA

Read More: Excel VBA: How to Set Print Area Dynamically (7 Ways)


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


Conclusion

To sum up, we have discussed 5 examples to set print areas for multiple ranges using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo