Freeze Panes Remover: Reset Every Awkward Scroll Position at Once

TL;DR: You open an inherited workbook and every sheet is frozen at someone else’s scroll position — row 47 on a 20-row schedule, column Q on a sheet that ends at column E. This macro unfreezes every sheet in one pass and tells you which ones it changed. Paste once, run whenever a workbook feels broken.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with freeze panes in awkward places — inherited files, multi-preparer workpapers, anything that’s been through more than one set of hands

What the macro does:

• Activates each sheet, checks ActiveWindow.FreezePanes, and clears it if found
• Reports the count and names of every sheet it changed
• Restores the original active sheet so you land back where you started

What the macro does NOT do:

• It does not modify cell values, formulas, formatting, or data of any kind
• It does not protect or unprotect sheets
• It does not change print settings, zoom levels, or any other view properties

Limitations:

• Freeze panes are a window-level property in Excel VBA. The macro must activate each sheet to check and clear them, which causes brief flickering even with ScreenUpdating = False
• Extremely hidden sheets (xlSheetVeryHidden) are skipped — you can’t activate a very-hidden sheet through normal VBA. Unhide it first with Unhide All Sheets
• Hidden sheets (xlSheetHidden) are also skipped since they can’t be activated without unhiding them first. If you want to check hidden sheets too, unhide them before running
• The macro reports current state when it runs. If you re-freeze a sheet later, running the macro again will catch it

#The Macro

Option Explicit

Sub RemoveAllFreezePanes()
    ' ── Remove All Freeze Panes ────────────────────────
    ' Loops every visible sheet and removes freeze panes.
    ' Reports which sheets were changed. Non-destructive
    ' — you can re-freeze any sheet at any time.
    '
    ' Zero input. Works on any workbook.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim startWS As Worksheet
    Dim count As Long
    Dim msg As String

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

    ' ── Remember where we started ──────────────────────
    Set startWS = ActiveSheet

    count = 0
    msg = ""

    ' ── Scan every sheet ───────────────────────────────
    For Each ws In ThisWorkbook.Worksheets
        ' ── Skip hidden sheets ─────────────────────────
        If ws.Visible <> xlSheetVisible Then _
            GoTo NextSheet

        ws.Activate

        If ActiveWindow.FreezePanes Then
            ActiveWindow.FreezePanes = False
            count = count + 1
            msg = msg & "  • " & ws.Name & vbCrLf
        End If

NextSheet:
    Next ws

    ' ── Return to original sheet ───────────────────────
    startWS.Activate

    ' ── Report results ─────────────────────────────────
    If count = 0 Then
        MsgBox "No freeze panes found on any sheet.", _
               vbInformation, "Remove All Freeze Panes"
    Else
        MsgBox "Removed freeze panes from " & count & _
               " sheet(s):" & vbCrLf & vbCrLf & msg, _
               vbInformation, "Remove All Freeze Panes"
    End If

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

#Why you must activate each sheet

Freeze panes in Excel VBA are a window property, not a worksheet property. You can’t check ws.FreezePanes — that property doesn’t exist on the Worksheet object. Instead, you check ActiveWindow.FreezePanes, which only reflects the currently active sheet.

ws.Activate
If ActiveWindow.FreezePanes Then
    ActiveWindow.FreezePanes = False

This is an unusual VBA pattern — most sheet operations don’t require activation. It’s the reason for Application.ScreenUpdating = False: without it, activating every sheet causes visible tab-switching as Excel redraws each one. With screen updating off, the activation happens invisibly and the macro completes in a fraction of a second.

#Why hidden sheets are skipped

A hidden sheet can’t be activated through normal VBA. Calling ws.Activate on a hidden sheet raises a runtime error. The macro checks ws.Visible before attempting activation:

If ws.Visible <> xlSheetVisible Then GoTo NextSheet

Very-hidden sheets (xlSheetVeryHidden, value 2) are also skipped for the same reason — they’d raise an error on activation. If you suspect a very-hidden sheet has freeze panes, unhide it first with the Unhide All Sheets macro, then run this one.

#Preserving the starting point

The macro remembers which sheet was active before it started and restores it at the end:

Set startWS = ActiveSheet
' ... loop through sheets ...
startWS.Activate

Without this, the last sheet in the workbook remains active after the macro finishes. If you were working on the TB sheet and the macro leaves you on “Notes” (the last tab alphabetically), you waste time tabbing back. Restoring the starting sheet is a small courtesy that makes the macro invisible in daily use.

#The message box as audit trail

Rather than a simple count, the macro lists every sheet it changed:

Removed freeze panes from 4 sheet(s):

  • TB
  • Sched-A Income
  • Depreciation
  • Sched-L Balance Sheet

This matters because freeze panes can be hard to notice. If you expected the macro to remove freeze panes from 6 sheets but it only reports 4, you know two sheets either didn’t have freeze panes or were hidden and skipped. The named list lets you verify each one.

#Adapt It