How to Clear Print Area through Multiple Sheets At Once in Excel?

In an excel workbook, we have the provision to set the print area (which we want to print instead of the complete workbook) from a sheet. In this article we will learn how to remove the print area if there are multiple sheets and all have predefined print area.

This can be done automatically by using the Visual Basics Applications for Excel. Follow the below steps for the same.

Removing Print Area of a complete Workbook using the VBA Code

Step 1: Below is the sample data with two different sheets having predefined Print Areas.

Step 2: Press Alt+F11 keys from the keyboard and the Microsoft Visual Basic for Applications window will open.

The above editor can also be opened using the Developer’s tab as shown below:

Step 2: In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.

Step 3: Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.

Sub ClearPrintArea() /A function is being called to clear the predefined print area.
    Dim ws As Worksheet /returns a Sheet collection object that only contains the worksheets
    For Each ws In Worksheets / specifying each sheet of a worksheet or workbook
        ws.PageSetup.PrintArea = "" / condition to check if any print area defined in any sheet, if Yes then disable it.
    Next / check the next sheet within the same workbook.
End Sub / end of subs.

Step 4: Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.

Step 5: Next, save the file in the format as, Excel Macro-Enabled Workbook.

Step 6: Now, go back to the file and press Alt+F8, the Macro dialog box will open as shown below.

Note: While selecting a file in which changes to be applied please consider the following points:

• If selected ‘All Open Workbooks’ it will remove print area from all the open excel files.

• If selected ‘This Workbook’ it will remove print area from the active sheet of respective workbook.

• If selected ‘<Workbook Name>’ it will remove print area from all sheets of respective workbook.

Step 7: Now all the print areas will be removed.

Using this method, you can remove pre-defined print area either from all open workbooks or only from the active workbook. However, to remove the specific sheet’s print area you can select Page Layout > Select Page Area under Page Setup > Select Clear Print Area. This method will remove print area only from the active sheet.