Print Area Clearer: Fix Your Printing When Every Sheet Shows a Different Random Range

TL;DR: You open an inherited workbook, hit Ctrl+P to print the partner review copy, and sheet after sheet prints only a random slice of the data — rows 1–47 here, columns A–J there, an empty corner on the next one. The prior preparer set print areas and never cleared them. There is no “clear all print areas” button in Excel. This macro finds every print area in the workbook, shows you exactly which sheets are affected, and clears them all with one confirmation click.

The Problem

You’ve finalized the Henderson engagement review and you’re ready to print the entire workpackage for the partner. You hit Ctrl+P on the first sheet. Preview looks good. You flip to the Fixed Assets sheet — it shows rows 1 through 47 and columns A through D, cutting off the accumulated depreciation column in E. Sched-B shows only rows 1–22 even though data goes to row 112. The State Apportionment sheet prints a single cell: D38. What?

A print area is Excel’s way of saying “only print this specific range.” It’s useful when you set it intentionally — print just the asset detail, skip the supporting calculations in rows 50–200. But when someone else set it on your workbook and didn’t clear it, you get mysteriously truncated printouts and don’t know why.

Excel’s only way to clear a print area is Page Layout → Print Area → Clear Print Area, one sheet at a time. For a 20-sheet workpackage, that’s 60 clicks — and you have to remember which sheets have them. There’s no list, no count, no way to know which sheets are affected without checking every single one.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with leftover print areas — any inherited file where Ctrl+P shows less than the full sheet, or where printouts are mysteriously truncated

What the macro does:

Loops every sheet in ThisWorkbook and checks if a print area is defined
Lists every sheet with a print area by name in a confirmation message box
Asks once before clearing — one confirmation, not one per sheet
Clears the print area on every sheet, returning them to “print what’s visible”

What the macro does NOT do:

It does not change page setup settings like margins, orientation, or scaling. Only the print area range is cleared
It does not change cell values, formulas, or formatting
It does not set new print areas. After running, each sheet prints its entire used range (the default Excel behavior when no print area is set)

Limitations:

Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook (PERSONAL.XLSB) to run on any open file
The ws.PageSetup.PrintArea property only exists at the sheet level. There is no workbook-level print area collection — you must loop each sheet
Hidden sheets are included. If a hidden sheet has a print area, it appears in the list and gets cleared alongside visible sheets
This macro does not reset the print area after clearing. If you accidentally clear an intentional print area, you’ll need to set it up again manually (Page Layout → Print Area → Set Print Area)

#The Macro

Option Explicit

Sub ClearAllPrintAreas()
    ' ── Print Area Clearer ────────────────────────────
    ' Scans every sheet for defined print areas, lists
    ' which sheets are affected, and clears them all
    ' with one confirmation.
    '
    ' Zero configuration. One confirmation. Prints right.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim affectedCount As Long
    Dim affectedSheets As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Find print areas on all sheets ──────────
    affectedCount = 0
    affectedSheets = ""

    For Each ws In ThisWorkbook.Worksheets
        If Len(ws.PageSetup.PrintArea) > 0 Then
            affectedCount = affectedCount + 1
            affectedSheets = affectedSheets & "  • " & ws.Name & _
                             " (" & ws.PageSetup.PrintArea & ")" & vbCrLf
        End If
    Next ws

    ' ── Step 2: Report findings ────────────────────────
    If affectedCount = 0 Then
        MsgBox "No print areas defined on any sheet." & vbCrLf & _
               "All sheets will print their full data.", _
               vbInformation, "All Clear"
        GoTo CleanUp
    End If

    Dim msg As String
    msg = "Found print area(s) on " & affectedCount & _
          " sheet(s):" & vbCrLf & vbCrLf & affectedSheets & vbCrLf & _
          "Clear ALL print areas? Each sheet will print its full" & _
          " data range after this."

    ' ── Step 3: Confirm and clear ──────────────────────
    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm — Clear All Print Areas") = vbNo Then
        MsgBox "No print areas were cleared.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    For Each ws In ThisWorkbook.Worksheets
        If Len(ws.PageSetup.PrintArea) > 0 Then
            ws.PageSetup.PrintArea = ""
        End If
    Next ws

    ' ── Step 4: Final report ───────────────────────────
    MsgBox "Cleared print areas on " & affectedCount & _
           " sheet(s). All sheets will now print their " & _
           "full used ranges.", vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#`ws.PageSetup.PrintArea` — the one property that controls everything

Every sheet in Excel has a PageSetup object. The PrintArea property on that object is either:

An empty string — no print area is set. The sheet prints everything from A1 to the last used cell
A range string — e.g., "$A$1:$F$48". Only that rectangle prints

If Len(ws.PageSetup.PrintArea) > 0 Then
    affectedCount = affectedCount + 1
    affectedSheets = affectedSheets & "  • " & ws.Name & _
                     " (" & ws.PageSetup.PrintArea & ")" & vbCrLf
End If

The macro checks each sheet for a non-empty PrintArea string. If it finds one, it records the sheet name and the range so the user can see what is set before deciding to clear it. A print area of $A$1:$F$48 is very different from $D$38 — the former might be intentional (printing the first 48 rows of a schedule), the latter is almost certainly a mistake.

Clearing is a one-liner:

ws.PageSetup.PrintArea = ""

Setting PrintArea to an empty string returns the sheet to default behavior — it prints whatever data is in the used range.

#Count first, clear later — the standard pattern

Like every “destructive bulk action” macro on this blog, the macro runs in two passes separated by a confirmation message box. The first pass is read-only:

For Each ws In ThisWorkbook.Worksheets
    If Len(ws.PageSetup.PrintArea) > 0 Then
        affectedCount = affectedCount + 1
        affectedSheets = affectedSheets & "  • " & ws.Name & _
                         " (" & ws.PageSetup.PrintArea & ")" & vbCrLf
    End If
Next ws

If the user clicks No, nothing is touched. The first pass only builds the inventory. The second pass — only reached after a Yes click — does the actual clearing.

#Why the message box shows the print area range

A bare “Found 6 print areas. Clear all?” message gives the user no information to assess the risk. The sixth print area on Sched-C Income might be a deliberately set range ($A$1:$J$28) that the prior preparer configured to exclude supporting calculations below row 28. Clearing it means the partner review copy now prints rows 29–120 too — maybe not what you want.

By showing the range, the user can spot-check: “Wait — Fixed Assets shows $A$1:$D$47 but the column for accumulated depreciation is column E. That’s almost certainly a mistake. But Sched-C shows $A$1:$J$28 and the data ends at row 28 — that looks intentional.”

affectedSheets = affectedSheets & "  • " & ws.Name & _
                 " (" & ws.PageSetup.PrintArea & ")" & vbCrLf

If you want to preserve some print areas and clear others, see the Adapt It section for a sheet-by-sheet prompt variant.

#No `Calculation` toggle needed

Clearing print areas doesn’t write to cells or change values. It only modifies a PageSetup metadata property. No formulas recalculate, no values change, no cell content is touched. The Application.Calculation toggle adds two lines for zero performance benefit.

Application.ScreenUpdating = False is included for consistency, though its impact here is minimal — clearing print areas doesn’t trigger visible screen redraws. It’s left in as a safety net in case future Excel versions change this behavior.

#The confirmation uses `vbExclamation` — same design principle

If MsgBox(msg, vbExclamation + vbYesNo, ...) = vbNo Then

Clearing print areas is a destructive action — there’s no built-in undo that restores a specific print area. The user would have to manually recreate any intentional print areas that were cleared. The yellow warning icon signals “pay attention — this changes something.” After running several macros from this blog, readers learn the color coding instinctively.

#Adapt It

Run on the active workbook instead of the macro workbook. Store this macro in your Personal Macro Workbook (PERSONAL.XLSB) and replace ThisWorkbook with ActiveWorkbook:

For Each ws In ActiveWorkbook.Worksheets

If no workbook is open, the error handler catches it and shows a friendly message.

Prompt sheet by sheet instead of clearing all at once. Replace the bulk confirmation with a per-sheet prompt so you can keep intentional print areas and clear the accidental ones:

For Each ws In ThisWorkbook.Worksheets
    If Len(ws.PageSetup.PrintArea) > 0 Then
        Dim response As VbMsgBoxResult
        response = MsgBox("Sheet '" & ws.Name & "' has print area: " & _
                  ws.PageSetup.PrintArea & vbCrLf & vbCrLf & _
                  "Clear it?", vbYesNo + vbQuestion, _
                  "Print Area — " & ws.Name)
        If response = vbYes Then
            ws.PageSetup.PrintArea = ""
            affectedCount = affectedCount + 1
        End If
    End If
Next ws

This is more clicks but gives you granular control. Good for workbooks where some print areas are intentional.

Set a uniform print area on every sheet instead of clearing. Replace the clearing logic with a range selection prompt. The user selects a range on the active sheet and the macro applies that same address to every sheet:

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox( _
    "Select the range to use as the print area on ALL sheets:", _
    "Set Uniform Print Area", Type:=8)
On Error GoTo CleanUp
If rng Is Nothing Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.PrintArea = rng.Address
Next ws

MsgBox "Set print area to " & rng.Address & _
       " on " & ThisWorkbook.Worksheets.Count & " sheet(s).", _
       vbInformation

Clear print areas AND standardize page setup. Chain this macro with the Print-Ready All Sheets macro. First clear all print areas (this macro), then run Print-Ready to set uniform margins, orientation, and scaling on every sheet:

' Run first: ClearAllPrintAreas  → removes per-sheet print areas
' Run second: PrintAllSheets     → sets landscape, fit-to-page, narrow margins

Skip specific sheets. Add a name-based filter to protect sheets you know have intentional print areas:

For Each ws In ThisWorkbook.Worksheets
    If Len(ws.PageSetup.PrintArea) > 0 Then
        If ws.Name Like "Print*" Or ws.Name = "Dashboard" Then
            ' Skip — these print areas are intentional
        Else
            ws.PageSetup.PrintArea = ""
            affectedCount = affectedCount + 1
        End If
    End If
Next ws

See also: Print-Ready All Sheets — sets uniform print settings (landscape, fit to one page, narrow margins) on every sheet. Run it after this macro to get consistent printouts. Hyperlink Stripper — same “scan first, delete after confirmation” pattern for hyperlinks.

Download the sample workbook (10 sheets — 6 with random print areas set, 4 without)

xlsx 18 KB