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

Get FREE Advanced Excel Exercises with Solutions!

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


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

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: Excel VBA: Print Preview for Selected Range


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: Excel VBA to Display Print Preview for Multiple Sheets


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 Specific Sheets Using VBA Macro in Excel


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


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


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.


Download Practice Workbook

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


Conclusion

To sum up, we have discussed 5 examples to set print area 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.


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:

Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
  1. how can I change the value in multiple cell,
    For example,
    i have data of 20 student’s like
    1. Name, 2. Roll Number, 3. subject and 4. semester. and this all data is on sheet 2, and I want to print each student’s data automatically in a same format front page.

    • Hello HBING,
      To solve your issue follow the steps given below.
      • Firstly, go to the Page Layout tab >> click on Print Titles.
      Print Data Automatically
      • After that, type 5:5 as Print Area and $2:$2 as Rows to repeat at top.
      • Then, click on OK.
      Opening Page Setup Box
      • Now, write the following code in your module.
      Sub Print_Each_Data()
      Dim iRng As Range
      Dim iWorkRng As Range
      Dim ixWs As Worksheet
      On Error Resume Next
      xTitleId = "Microsoft Excel"
      Set iWorkRng = Application.Selection
      Set iWorkRng = Application.InputBox("Range", xTitleId, iWorkRng.Address, Type:=8)
      Set ixWs = iWorkRng.Parent
      For Each iRng In iWorkRng
      ixWs.PageSetup.PrintArea = iRng.EntireRow.Address
      ixWs.PrintPreview
      Next
      End Sub
      VBA Code
      • Next, click on Macros from the Developer tab.
      • Select the macro named Print_Each_Data.
      • Lastly, click on Run.
      Choose Macro
      • Now, a box will open.
      • Then, select the range which you want to print. Here, we selected cell range B2:E3.
      • Finally, click on OK.
      Select Range
      • Thus, you can print each student’s data automatically in the same format.
      Print Preview
      If you face any further problems, please share your Excel file with us in the comment section.
      Regards
      Arin Islam,
      Exceldemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo