Introduction

Freezing panes is a very handy feature that is indispensable in any Excel workbook that has more than few columns or few rows.  In this article, we will take a look at exactly how to utilize the Freeze Pane feature in both Excel and VBA.

Freeze a Row in Excel

If you want to freeze a row or a number of rows, meaning to be able to keep them visible as you scroll down, then do the following:

1. Select the entire row below the last row of the rows you want to freeze.
2. Go to View –> Freeze Panes –> Freeze Panes

Alternatively, you can select ‘Freeze Top Row’ if you want to only freeze the first row of the worksheet.

Freeze a Column in Excel

Similarly, you can apply the same method if you want to freeze a column or a number of columns, enabling you to keep them visible as you scroll to the right.

Of course you can use ‘Freeze First Column’ if you only want to freeze the first column.

Freezing Both Rows and Columns

If you want to freeze both rows and columns, select a single cell where the rows you want to freeze are above it and the columns you want to freeze are below it. In other words, the top-left corner cell of the range will remain unfrozen.

Split Panes in Excel

Other than freezing panes in Excel, you can also split panes. This duplicates the Excel sheet into multiple panes as shown below.

Freeze Panes Using VBA

To freeze a pane, make the selection of the range that you want to freeze based on, whether it’s rows, columns, or a cell, then use FreeePanes = True

Sub Freeze_Panes()
'first, ensure that no panes are frozen
ActiveWindow.FreezePanes = False
'select the row that you want to freeze based on
Rows(“3:3”).select
'freeze panes
ActiveWindow.FreezePanes = True
End Sub

Split Panes Horizontally Using VBA

You can use SplitRow to split panes by rows. The value you set for the split row is the number of the top rows below which the screen will be split.

ActiveWindow.SplitRow = 2

Split Panes Vertically Using VBA

You can use SplitColumn to split panes by columns. The value you set for the split column is the number of the left columns below which the screen will be split.

ActiveWindow.SplitColumn = 2

Also you can split panes by both rows and columns:

ActiveWindow.SplitColumn = 2
ActiveWindow.SplitRow = 4

Summary

In this guide, we covered how to freeze or split panes based on a row, a column, or a cell. We saw how we can do it in Excel manually, and we also saw how to automate it using a VBA script.