How to Freeze Panes with VBA in Excel (5 Suitable Ways)

In this article, I’ll show you how can freeze the panes of an Excel worksheet with Visual Basic of Applications (VBA). Many a time while working in Excel, we need to freeze the panes of the worksheet for convenience and a better experience. Today you’ll learn how you can accomplish this with VBA.

Freeze Panes with VBA in Excel (Quick View)

Sub Freeze_Panes_Row_and_Column()

Range("C4").Select

ActiveWindow.FreezePanes = True

End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

An Introduction to Excel Freeze Panes

In Microsoft Excel, to freeze panes means to freeze a row or a column or both in such a way that even if you go down or right through scrolling the scrollbar, that row or column will always be visible. It’s normally done with rows or columns that contain the headers of the data set.

For example, look at the data set below.  Here we’ve frozen the worksheet up to row 3 (Years) and column B (Products Name).

When we’ll go down the worksheet by scrolling the scrollbar, we’ll find that the rows up to row 3 are always visible.

Same for column B while we scroll right.

Now, to freeze panes in a worksheet manually, select the cell just after the row and column (Cell C4 in this example) and go to View > Freeze Panes > Freeze Panes in the Excel toolbar.

To freeze only the row, select the entire row and go to View > Freeze Panes > Freeze Panes in the Excel toolbar.

Similarly, to freeze only the column, select the whole column and go to View > Freeze Panes > Freeze Panes in the Excel toolbar.

⧭ Notes:

• Select Freeze Top Row to freeze only the top row.
• Similarly, select Freeze First Column to freeze only the first column.

5 Methods to Freeze Panes with VBA in Excel

We’ve learned what is to freeze panes in Excel and how to accomplish it manually. Now, let’s go to our main discussion today, how to freeze panes with VBA.

1. Freeze Only a Row with VBA in Excel

First of all, let’s see how we can freeze only a row with VBA.

As discussed earlier, to freeze only a row, first you’ve to select the entire row below the row to be frozen (Row 4 in this example).

Then you’ve to apply the Freeze Panes command.

So the VBA code will be:

⧭ VBA Code:

Sub Freeze_Panes_Only_Row()

Range("C4").EntireRow.Select

ActiveWindow.FreezePanes = True

Range("C4").Select

End Sub

⧭ Output:

Run this code. And you’ll find the active worksheet frozen up to row 3.

⧭ Notes:

• Here we’ve used cell C4 to select any cell of row 4 of the worksheet. You select it according to your need.
• The last line of the code Range(“C4”).Select is for the purpose of deselecting the entire row 4 (Deselecting any selection means selecting a new selection, as in Excel, something must remain selected). You can omit this line if you want.

Read More: How to Freeze Top Row in Excel (4 Easy Methods)

2. Freeze Only a Column with VBA in Excel

We’ve seen how we can freeze a row with VBA. Now let’s see how to freeze a column with VBA.

Similar to the row, to freeze only a column, first you’ve to select the entire column right to the column to be frozen (Column C in this example).

Then you’ve to apply the Freeze Panes command.

So the VBA code will be:

⧭ VBA Code:

Sub Freeze_Panes_Only_Column()

Range("C4").EntireColumn.Select

ActiveWindow.FreezePanes = True

Range("C4").Select

End Sub

⧭ Output:

Run this code. And you’ll find the active worksheet frozen up to column C.

⧭ Notes:

• Here we’ve used cell C4 to select any cell of column C of the worksheet. You select it according to your need.
• The last line of the code Range(“C4”).Select is for the purpose of deselecting the entire column C (Deselecting any selection means selecting a new selection, as in Excel, something must remain selected). You can omit this line if you want.

Read More: How to Freeze 2 Columns in Excel (5 Methods)

3. Freeze both Row and Column with VBA in Excel

We’ve seen how we can freeze a row and a column separately. This time, let’s see how we can freeze both the row and the column together.

To freeze both the row and the column together, you have to select a cell below the row to be frozen and right to the column to be frozen (Cell C4 in this example).

Then you’ve to apply the Freeze Panes command.

So the VBA code will be:

⧭ VBA Code:

Sub Freeze_Panes_Row_and_Column()

Range("C4").Select

ActiveWindow.FreezePanes = True

End Sub

⧭ Output:

Run this code. And you’ll find the active worksheet frozen up to row 3 and column C.

⧭ Notes:

• Here we’ve used cell C4 to select a cell below row 3 and right to column B. That’s cell C4. You select it according to your need.

Read More: How to Freeze Selected Panes in Excel (10 Ways)

Similar Readings:

• How to Freeze Multiple Panes in Excel (4 Criteria)
• Keyboard Shortcut to Freeze Panes in Excel (3 Shortcuts)
• How to Freeze First 3 Columns in Excel (4 Quick Ways)

4. Develop a Userform to Freeze Panes with VBA in Excel

We’ve seen how we can freeze a row or a column or both the row and column with VBA in an Excel worksheet.

Now we’ll develop a Userform to bring all the distinct tasks within a single interface.

⧭ Step by Step Procedure to Develop the Userform:

⧪ Step 1:

• Press ALT+F11 on your keyboard to open the Visual Basic
• In the Visual Basic editor, go to Insert > UserForm to insert a new Userform.

⧪ Step 2:

• A new UserForm called UserForm1 will be created in the VBA
• On the left of the UserForm, you’ll get a ToolBox called Control. Hover your mouse on the toolbox and search for a TextBox (TextBox1). After finding one, drag it over the top of the UserForm.
• Similarly, drag a ListBox (ListBox1) right to the Textbox, and a CommandButton (Commandbutton1) to the bottom right corner of the UserForm. Change the display of the CommandButton to OK. Your UserForm should now look like this:

⧪ Step 3:

Insert a Module (Insert > Module) from the VBA toolbox

⧪ Step 4:

Insert the following VBA code in the Module.

Sub Run_UserForm()

UserForm1.Caption = "Freeze Panes"

UserForm1.TextBox1.Text = Selection.Address
UserForm1.TextBox1.BorderStyle = fmBorderStyleSingle

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption

UserForm1.ListBox1.AddItem "1. Freeze Row"
UserForm1.ListBox1.AddItem "2. Freeze Column"
UserForm1.ListBox1.AddItem "3. Freeze Both Row and Column"

Load UserForm1
UserForm1.Show

End Sub

⧪ Step 5:

Double click on the CommandButton displayed as OK. A Private Sub called CommandButton1_Click will open. Insert the following code there:

    If UserForm1.ListBox1.Selected(0) = True Then
        Set Rng = Selection
        Rng.EntireRow.Select
        ActiveWindow.FreezePanes = True
        Rng.Select

    ElseIf UserForm1.ListBox1.Selected(1) = True Then
        Set Rng = Selection
        Rng.EntireColumn.Select
        ActiveWindow.FreezePanes = True
        Rng.Select

    ElseIf UserForm1.ListBox1.Selected(2) = True Then
        ActiveWindow.FreezePanes = True

    Else
        MsgBox "Select At Least One. ", vbExclamation

    End If

    Unload UserForm1

⧪ Step 6:

Similarly double click on the TextBox1. A Private Sub called TextBox1_Change will open. Insert the following code there.

Private Sub TextBox1_Change()

On Error GoTo Message

Range(TextBox1.Text).Select

Message:
    Note = 5

End Sub

⧪ Step 7:

Your UserForm is now ready to use. Select the cell below to the row to be frozen and right to the column to be frozen (Cell C4 here), and run the Macro called Run_UserForm.

⧪ Step 8:

• The UserForm will be loaded. You’ll find the address of the selected cell (C4) in the TextBox. If you want, you can change this.
• Then select any one of the three options available in the ListBox. Here I want to freeze both row and column, so I’ve selected Freeze Both Row and Column.
• Then click OK.

⧪ Step 9:

You’ll find the worksheet frozen according to your desire. (Here frozen up to row 3 and column B).

Related Content: How to Freeze Frame in Excel (6 Quick Tricks)

5. Alternative of the Freeze Panes in Excel: Split the Window with VBA

We’ve talked a lot about the freeze panes in Excel. Now, let’s see a very useful alternative to the freeze panes in Excel, the Split Window command.

You can ActiveWindow.SplitRow or ActiveWindow.SplitColumn in VBA to split the worksheet row-wise or column-wise.

For example, to split the worksheet from row 3, use:

ActiveWindow.SplitRow = 3

Similarly, to split the worksheet from column B, use:

ActiveWindow.SplitColumn = 2

⧭ VBA Code:

Sub Split_Window()

ActiveWindow.SplitRow = 3

ActiveWindow.SplitColumn = 2

End Sub

⧭ Output:

Run the code, it’ll split the active worksheet from row 3 and column B.

Related Content: How to Apply Custom Freeze Panes in Excel (3 Easy Ways)

Things to Remember

• Before applying Freeze Panes in Excel, you must Unfreeze all the Freeze Panes already applied. Otherwise, the Freeze Panes command won’t work.
• The Freeze Panes command won’t work through the merged cells. So unmerge them before applying the Freeze Panes command if there is any.

Conclusion

So these are the methods to use Freeze Panes with VBA in Excel. I’ve tried to discuss all possible ways to apply Freeze Panes over a worksheet in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Freeze Top Two Rows in Excel (4 ways)
• Freeze Header in Excel (Top 4 Methods)
• How to Freeze Top 3 Rows in Excel (3 Methods)
• Excel Freeze Panes Not Working (5 Causes with Fixes)