VBA to Freeze Top Row in Excel

In Excel, you can utilize VBA to freeze the top row of a worksheet, which is usually the header row.

This action can provide various benefits, such as a consistent reference point as you scroll through the worksheet, eliminating the need to repeatedly scroll up to view column headers, saving you time and effort.

In this tutorial, I will show you four ways to utilize the ‘Window.FreezePanes’ property to freeze the top row in Excel using VBA.

‘Window.FreezePanes’ Property

The ‘Window.FreezePanes‘ property is used in Excel to lock specific rows and/or columns in place to remain visible while scrolling through the rest of the worksheet.

This locking can be helpful when working with large datasets to keep headers or essential information visible at all times for quick reference.

The ‘Window.FreezePanes’ property is a Boolean value set to ‘True’ to freeze panes in the active window or ‘False’ to unfreeze the panes.

The ‘Window.FreezePanes’ property only applies to Excel worksheets and macro sheets.

The syntax of the property:

expression.FreezePanes 

Where expression is a variable representing the ‘Window’ object.

Freeze the Top Row Using Window.FreezePanes Property

Suppose you have the dataset below on the active worksheet:

You can use the VBA code below to freeze the header row:

Sub FreezeTopRow()

With ActiveWindow

.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True

End With

End Sub

When you execute the code, it locks the header row in place so that it remains visible as you scroll through the rest of the worksheet:

Notice the horizontal split bar below the frozen header row.

Explanation of the Code

The following is an explanation of some of the code’s statements:

1. ‘ActiveWindow.SplitColumn = 0’: This line sets the ‘SplitColumn’ property of the active window to 0. The ‘SplitColumn’ property determines the number of columns to the left of the split line. In this case, the property is set to 0, meaning there is no column to the left of the split line, and all columns will scroll with the rest of the worksheet.
2. ActiveWindow.SplitRow = 1: This statement sets the ‘SplitRow’ property of the active window to 1. The ‘SplitRow’ property determines the number of rows above the split line. Setting it to 1 means the code freezes the top row, and the row won’t scroll with the rest of the worksheet.
3. ‘ActiveWindow.FreezePanes = True’: This line sets the ‘FreezePanes’ property of the active window to ‘True.’ This setting freezes the panes based on the values set for ‘SplitColumn’ and ‘SplitRow.’ In this case, it freezes the top row (row 1)above the split line and no columns (0) to the left of the split line.

Also read: How to Unhide All Rows in Excel with VBA

Freeze Top Row Using ‘Rows.Select’ Method with ‘Window.FreezePanes’ Property

Suppose you have the dataset below on the active worksheet:

You can use the below VBA code to freeze the top row:

Sub SelectRowFreezeTopRow()

ActiveSheet.Rows("2:2").Select

ActiveWindow.FreezePanes = True

End Sub

When you run the subroutine, it freezes the header row so that it remains visible as you navigate the worksheet:

Notice the horizontal split bar below the frozen header row.

Explanation of the Code

The VBA code selects the second row of the active sheet and then freezes the panes to keep the row above the selected row (row 1) visible at the top of the worksheet while scrolling.

Also read: VBA to Delete Rows Based On Cell Value

Freeze To Row Using ‘Range.Select’ Method with ‘Window.FreezePanes’ Property

Suppose you have the dataset below on Sheet2:

You can use the below VBA code to freeze the top row:

Sub SelectRangeFreezeTopRow()

Worksheets("Sheet1").Activate

Range("A2").Select

ActiveWindow.FreezePanes = True

End Sub

When you execute the subroutine, it freezes the header row so that it remains visible as you scroll through the worksheet:

Notice the horizontal split bar below the frozen header row.

Explanation of the Code

The subroutine activates Sheet1, selects cell A2, and then freezes the active window’s top row (row 1) to keep it visible when scrolling.

Also read: Delete Blank Rows Using VBA

Freeze Top Row Using the ‘Cells.Select’ Method with ‘Window.FreezePanes’ Property

Suppose you have the dataset below on the active worksheet:

You can use the VBA subroutine below to freeze the header row:

Sub SelectCellFreezeTopRow()

ActiveSheet.Cells(2, 1).Select
ActiveWindow.FreezePanes = True

End Sub

When you execute the subroutine, it freezes the header row so that it remains visible as you navigate the worksheet:

Notice the horizontal split bar below the frozen header row.

Explanation of the Code

The VBA code uses the select method of the ‘Cells’ property to select the cell in the second row and first column of the active worksheet and then freezes the panes to keep the row above the selected cell visible while scrolling.

Note: The ‘Cells’ property in VBA refers to a specific cell or a range of cells in a worksheet. The ‘Cells’ property takes two arguments: the row number and the column number, and it returns a reference to the cell at the specified location.

Also read: Count Rows using VBA in Excel

Benefits of Freezing the Top Row in Excel

The following are some of the benefits of freezing the top row in Excel:

1. The top row often contains column headers that provide context for the data in each column. Freezing the top row ensures that these headers remain visible even when you scroll down through a long list of data, making it easy to understand the content in each column.
2. Freezing the header row in large datasets allows for easy reference to column names while analyzing and editing data. This action is useful when you’re sorting, filtering, or applying formulas.
3. Freezing the top row provides a consistent reference point as you navigate the worksheet. The process eliminates the need to scroll back to the top to see column headers, saving time and effort.
4. Having the header row visible helps prevent errors when entering or editing data. You can easily verify the column names, ensuring you enter data into the correct columns.

In this tutorial, I showed you four methods of using VBA to freeze the top row in Excel. I hope you found the tutorial helpful.

Other articles you may also like:

• What is VBA in Excel?
• Freeze the Top Row and First Column in Excel
• VBA to Hide Rows Based On Cell Values
• Find Last Row Using VBA
• How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)