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