vba set non contiguous print in Excel

Q177. In Excel, how can I set non-contiguous print area using VBA?

For example, in my WorkSheet I want to print only 2 tables (one table range is "A2:D34" and another table range is "AM47:AQ60") leaving out the rest of the WorkSheet contents.

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub PrintSetUp()
  3. ActiveSheet.PageSetup.PrintArea = "A2:D34,AM47:AQ60"
  4. With ActiveSheet.PageSetup
  5. .CenterFooter = "Page &P of &N"
  6. .CenterHorizontally = True
  7. .CenterVertically = False
  8. .Orientation = xlLandscape
  9. .PaperSize = xlPaperA4
  10. .Zoom = 100
  11. End With
  12. End Sub

Description:

a) Line 3 - Each range to be printed should be specified with "," in between them as in "A2:D34,AM47:AQ60"".

 

You can find similar Excel Questions and Answer hereunder

1) How can I get row count of filtered data?

2) How can I update a listbox based on data in a list using VBA?

3) How can I avoid updates to cell values during macro execution?

4) How can I convert Column numbers into Column names for use in range definition?

5) How can I hide Formula Bar and Headings using VBA?

6) I have a complex voluminous workbook with lot of formulas. Every time I make an update, the processing is slow. Any workarounds available?

7) How do i put double quotes in a string in vba in Excel

8) String split in vba in Excel

9) How can I save a WorkSheet as a new WorkBook using VBA?

10) How do I restrict user entry in ActiveX Text Box to just numeric values?

11) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

12) How can I dynamically add a hyperlink using VBA?

13) How to add a link in a sheet to another sheet

14) How can I remove all nonprintable characters from text?

15) Vba list all files in a folder in Excel