Excel Macro: Clearing all Print Area in Active Workbook

This Excel VBA macro clears all the worksheets in an active workbook.

Macro Example

Sub ClearPrintArea_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.PageSetup.PrintArea = ""
  Next sh

End Sub

Description

Variable Declaration

The 3rd line declares an object variable called sh which will refer to a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Starts Looping

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets

The 5th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

[Application.]Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Clearing Print Area

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.PageSetup.PrintArea = ""

The 6th line clears the print area.

Worksheet.PageSetup

    sh.PageSetup...

The Worksheet.PageSetup property returns a PageSetup object that contains all the page setup settings for the worksheet.

obj-Excel-Worksheet-PageSetup

PageSetup.PrintArea

    sh.PageSetup.PrintArea...

The PageSetup.PrintArea property returns or sets the range to be printed.

obj-Excel-PageSetup-PrintArea

    sh.PageSetup.PrintArea = ""

In this macro, we clear the print aria by setting this property to the empty string (“”).

Loops Back

Sub ClearPrintArea_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.PageSetup.PrintArea = ""
  Next sh

The 7th line loops back to get the next worksheet.

After every worksheet is evaluated, this macro ends.

Sub ClearPrintArea_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.PageSetup.PrintArea = ""
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-clear-all-print-area.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007