[Excel VBA] How to Freeze Panes to Keep Headers Visible

Introduction

When working with large tables in Excel, scrolling down often hides the header rows, and scrolling right hides the item columns. This makes it difficult to understand what the data represents.

While Excel’s “Freeze Panes” feature solves this, you can automate this setting using VBA. This article explains the basic method to freeze panes using VBA and improve the usability of your tables.

Basic Rules for Freezing Panes

The most important rule when freezing panes in VBA is that it is based on the Active Cell (the currently selected cell).

• All rows above the active cell will be frozen.
• All columns to the left of the active cell will be frozen.

As long as you remember this rule, you can control the frozen position freely.

• To freeze only the top row -> Select cell A2.
• To freeze only the first column -> Select cell B1.
• To freeze both the top row and first column -> Select cell B2.

Complete VBA Code

Here are the codes for the three most common patterns.

1. Freeze Only the Top Row (Header Row)

Sub FreezeTopRow()
    ' Unfreeze panes first to ensure a clean state
    ActiveWindow.FreezePanes = False
    
    ' Select cell A2 (This freezes row 1)
    Range("A2").Select
    
    ' Freeze panes
    ActiveWindow.FreezePanes = True
End Sub

2. Freeze Only the First Column

Sub FreezeFirstColumn()
    ' Unfreeze panes first
    ActiveWindow.FreezePanes = False

    ' Select cell B1 (This freezes column A)
    Range("B1").Select
    
    ' Freeze panes
    ActiveWindow.FreezePanes = True
End Sub

3. Freeze Both the Top Row and First Column

Sub FreezeTopRowAndFirstColumn()
    ' Unfreeze panes first
    ActiveWindow.FreezePanes = False

    ' Select cell B2 (This freezes row 1 and column A)
    Range("B2").Select
    
    ' Freeze panes
    ActiveWindow.FreezePanes = True
End Sub

Code Explanation

Selecting the Reference Cell: Range("...").Select

As mentioned in the rules, selecting the reference cell is the first step. You must select the cell immediately to the bottom-right of the area you want to freeze (e.g., Range("B2").Select).

Executing the Freeze: ActiveWindow.FreezePanes = True

By setting the FreezePanes property of the ActiveWindow to True, the window is frozen based on the currently active cell.

Unfreezing: ActiveWindow.FreezePanes = False

To unfreeze the panes, set the same property to False. If you try to freeze panes while they are already frozen, it may cause unintended behavior. Therefore, as shown in the sample code, it is safer to unfreeze first before applying a new freeze.

Summary

The procedure for freezing panes in VBA is very simple:

1. Reset existing settings with ActiveWindow.FreezePanes = False.
2. Select the cell at the bottom-right of the desired freeze area (e.g., Range("B2").Select).
3. Execute ActiveWindow.FreezePanes = True.

By preparing this simple macro, you can ensure that your files are always easy to view for anyone who opens them.