vba set zoom tile rows footer in Excel

Q176. In Excel, how can I set Page orientation, Zoom % , Title Rows and footer using VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub PrintSetUp()
  3. ActiveSheet.PageSetup.PrintArea = "AP2:AR14"
  4. With ActiveSheet.PageSetup
  5. .PrintTitleRows = "$2:$2"
  6. .PrintTitleColumns = ""
  7. .CenterFooter = "Page &P of &N"
  8. .CenterHorizontally = True
  9. .CenterVertically = False
  10. .Orientation = xlLandscape
  11. .PaperSize = xlPaperA4
  12. .Zoom = 100
  13. End With
  14. End Sub


a) Line 5 - The rows specified (in the code above it is row 2) in Line 5 would appear in every page that is printed. In this example, since there is only one Page for Print, this command does not make a difference.

b) Line 6 - The columns specified (in the code above no column is specified as the print range does not span 2 pages) in Line 6 would appear in every page that is printed.

c) Line 7 - "&P" is the Page No and "&N" is the total number of pages. When the above code is executed, footer appears as "Page 1 of 1".

d) Line 10 - If Portrait printing is required, the field can be changed to xlPortrait instead of xlLandscape.


You can find similar Excel Questions and Answer hereunder

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

2) How can I filter and copy only filtered data using VBA?

3) How can I copy and rename a WorkSheet using VBA?

4) How can I set FreezePanes in a certain range using VBA?

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

6) How can I list all files in a folder using VBA?

7) Determine if hyperlinks are valid in Excel

8) How can I set the fill color, font color and set number format of cell to date?

9) How can worksheet functions be accessed in VBA?

10) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

11) How can I delete all shapes in a WorkSheet?

12) How can I add Trendline to a chart using VBA?

13) How to concatenate strings in vba in Excel

14) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

15) How can I hide all comments in my WorkSheet using VBA?