How to Freeze Panes Using Excel and VBA
Contents
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:
- Select the entire row below the last row of the rows you want to freeze.
- 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.