Table of Contents
Freezing panes using VBA allows you to lock certain rows and columns on a sheet so that they remain visible even when you scroll through the rest of the data. This can be useful when you want to have a header row or a couple of columns visible at all times. To freeze panes using VBA, you would use the FreezePanes property of the Application object. This is followed by the row and column numbers that you want to freeze, separated by a comma. For example, to freeze the first row and the first column, you would use Application.FreezePanes = 1,1.
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.