How to Freeze Panes Using VBA (With Examples)


You can use the following syntax in VBA to freeze specific panes in an Excel worksheet:

Sub FreezeCertainPanes()

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

End Sub

The .SplitColumn argument specifies the number of columns (starting from the left) to freeze.

The .SplitRow argument specifies the number of rows (starting from the top) to freeze.

This particular macro freezes only the first row in the Excel sheet.

The following examples show how to use this syntax in practice with the following Excel sheet:

Example 1: Freeze First Row Using VBA

We can create the following macro to freeze the first row in the Excel sheet:

Sub FreezeCertainPanes()

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

End Sub

Once we run this macro, the first row in the sheet becomes frozen:

No matter how far we scroll, the first row will remain visible.

Example 2: Freeze First Column Using VBA

We can create the following macro to freeze the first row in the Excel sheet:

Sub FreezeCertainPanes()

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 1
    .SplitRow = 0
    .FreezePanes = True
End With

End Sub

Once we run this macro, the first column in the sheet becomes frozen:

No matter how far we scroll, the first column will remain visible.

Example 3: Freeze Specific Rows and Columns Using VBA

We can create the following macro to freeze the first 3 rows and the first 2 columns in the Excel sheet:

Sub FreezeCertainPanes()

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 2
    .SplitRow = 3
    .FreezePanes = True
End With

End Sub

Once we run this macro, the first 3 rows and first 2 columns in the sheet becomes frozen:

No matter how far we scroll, the first 3 rows and first 2 columns will remain visible.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Find Last Used Row
VBA: How to Find Last Used Column
VBA: How to Insert Multiple Rows

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *