excel vba freeze pane without select

I have a VBA script in Excel that freezes the panes of an Excel worksheet, but I'm curious to see if this is possible without first selecting a range. Here's by code now which freezes rows 1 through 7:

ActiveSheet.Range("A8").Select ActiveWindow.FreezePanes = True 

Any suggestions?

9 Answers

Record yourself using the View ► Freeze Panes ► Freeze Top Row command and this is what you get for .FreezePanes.

With ActiveWindow If .FreezePanes Then .FreezePanes = False .SplitColumn = 0 .SplitRow = 1 .FreezePanes = True End With 

So modifying the .SplitColumn and/or .SplitRow properties should do it for you regardless on what the ActiveCell property is.

There are many things to get wrong about freezing panes. I add my own answer, so I will find it here, and won't have to reinvent it next time.

Public Sub FreezePanesAt(rngDataTopLeft As Range) Dim wndCurrent As Window: For Each wndCurrent In rngDataTopLeft.Worksheet.Parent.Windows With wndCurrent .FreezePanes = False If Not ((rngDataTopLeft.Row = 1) And (rngDataTopLeft.Column = 1)) Then .ScrollRow = 1 .ScrollColumn = 1 .SplitRow = rngDataTopLeft.Row - 1 .SplitColumn = rngDataTopLeft.Column - 1 .FreezePanes = True End If End With Next End Sub 

Example usage:

FreezePanesAt ThisWorkbook.Worksheets("Sheet1").Range("B3") FreezePanesAt ThisWorkbook.Names("Header").RefersToRange 

• The input parameter is the top left cell of the bottom right pane; I think this is the most frequent use case: you know the range at which to split and don't care about which workbook / worksheet / window it is in
• If the input parameter is in the first row / first cell but not A1, then there will be only two panes; A1 is a special case, however, Excel would split the window at center of the current view, I prevented this because I can't think of any case where this would be intended
• It iterates through all Windows attached to the workbook / worksheet; indexing into Application.Windows (Windows(Thisworkbook.Name)) won't cause an error if you have more windows to the same workbook (the name would be "MyWorkbook:1"), or Excel attempted (which usually fails) to repair a workbook after a crash (the name would be "MyWorkbook [Repaired]")
• It takes into consideration that panes may already be frozen and the user / another macro might have scrolled to a location in the workbook, and the top left cell in the window is not A1

I found the previous answers only worked with some sheets when looping through tabs. I found the following code worked on every tab I looped through (target was a single workbook), despite which workbook was the activeworkbook.

The short of it:

With Application.Windows(DataWKB.Name) Application.Goto ws.Cells(4, 5) .SplitColumn = 4 .SplitRow = 3 .FreezePanes = True End With 

The code as it is in my Sub: (be aware, I do a lot more formatting in this sub, I tried to strip that out and leave just the code needed here)

Sub Format_Final_Report() Dim DataWKB As Workbook Set DataWKB = Workbooks("Report.xlsx") Dim ws As Worksheet Dim tabCNT As Long Dim tabName As String tabCNT = DataWKB.Sheets.Count For i = 1 To tabCNT Set ws = DataWKB.Worksheets(i) tabName = ws.Name With Application.Windows(DataWKB.Name) Application.Goto ws.Cells(4, 5) .SplitColumn = 4 .SplitRow = 3 .FreezePanes = True End With Next i End Sub 

Hopefully, this will save someone some research time in the future.

I need to be able to properly refreeze panes (when creating new windows, notably) without losing the activecell or messing up the visible range. It took a lot of playing around but I think I have something solid that works:

Sub FreezePanes(nbLignes As Integer, nbColonnes As Integer, Optional ByVal feuille As Worksheet) If feuille Is Nothing Then Set feuille = ActiveSheet Else feuille.Activate Error GoTo erreur With ActiveWindow If .View = xlNormalView Then If .FreezePanes Then .FreezePanes = False If .Split Then .Split = False .SplitColumn = nbColonnes .SplitRow = nbLignes If .Panes.Count = 4 Then 'rows and columns frozen .Panes(1).ScrollRow = 1 .Panes(1).ScrollColumn = 1 .Panes(2).ScrollRow = 1 'top right pane .Panes(3).ScrollColumn = 1 'bottom left pane ElseIf nbLignes > 0 Then .Panes(1).ScrollRow = 1 ElseIf nbColonnes > 0 Then .Panes(1).ScrollColumn = 1 Else: GoTo erreur End If .FreezePanes = True End If End With Exit Sub erreur: Debug.print "Erreur en exécutant le sub 'FreezePanes " & nbLignes & ", " & nbColonnes & ", '" & feuille.Name & "' : code #" & Err.Number & Err.Description End Sub 

I know this is old but I came across this tidbit that may be useful... as ChrisB stated, the SplitColumn/SplitRow values represent the last cell above/left of the split BUT of the currently visible window. So if you happen to have code like this:

Application.Goto Worksheets(2).Range("A101"), True With ActiveWindow .SplitColumn = 0 .SplitRow = 10 .FreezePanes = True End With 

The split will be between rows 110 and 111 instead of 10 and 11.

edited for clarification and to add more information:
My point is that the values are offsets of the upper left cell, not an address of a cell. Therefore, ChrisB's Dec 4 '15 at 18:34 comment under the main answer only holds if row 1 is visible in the Activewindow.

A couple of other points on this:

1. using Application.goto doesn't necessarily put whichever cell you are trying to go to in the upper left
2. the cell that is put in the upper left when using .goto can depend on the size of the excel window, the current zoom level, etc (so fairly arbitrary)
3. it is possible to have the splits placed so that you can not see them or even scroll around in the visible window (if .FreezePanes = true). for example:

Application.Goto Worksheets(1).Range("A1"), True With ActiveWindow .SplitColumn = 100 .SplitRow = 100 .FreezePanes = True End With 

CETAB may be dealing with this in their answer.

Yes, the ActiveWindow.ScrollRow = 1 and ActivWindow.ScrollColumn = 1 is a must for FreezePanes if your visible window does not include cell A1.

If you are freezing rows 1:3 by selecting row 4 or cell A4, and cell A3 is not visible, the FreezePanes function will freeze the window in the center of the visible window.

Also if cell B4 is selected, and column A is not visible, then only the rows 1:3 will be frozen (column A will not frozen). Similarly, if rows 1:3 are not visible, only column A will be frozen. If both column A and rows 1:3 are not visible, the FreezePanes function will freeze the window in the center of the visible window.

The problem with splitting is that if a user unfreezes panes, the panes will remain split. (I couldn't find a way to turn off split afterwards while keeping the panes frozen)

This may be too obvious/simple, but what if the current selection is simply saved and then re-selected afterwards?

Sub FreezeTopRow() 'First save the current selection to go back to it later Dim rngOriginalSelection As Range Set rngOriginalSelection = Selection 'Change selection to A2 to make .FreezePanes work ActiveSheet.Range("A2").Select ActiveWindow.FreezePanes = True 'Change selection back to original rngOriginalSelection.Select End Sub 

Here is what i use...

Public Sub FreezeTopRowPane(ByRef MyWs As Excel.Worksheet, _ Optional ByVal AfterRowNr As Integer = 1) Dim SavedWS As Worksheet Dim SavedUpdating As Boolean SavedUpdating = Application.ScreenUpdating 'save current screen updating mode Set SavedWS = ActiveSheet 'save current active sheet Application.ScreenUpdating = False 'turn off screen updating MyWs.Activate 'activate worksheet for panes freezing ActiveWindow.FreezePanes = False 'turn off freeze panes in case With ActiveWindow .SplitColumn = 0 'set no column to split .SplitRow = AfterRowNr 'set the row to split, default = row 1 End With ActiveWindow.FreezePanes = True 'trigger the new pane freezing SavedWS.Activate 'restore previous (saved) ws as active Application.ScreenUpdating = SavedUpdating 'restore previous (saved) updating mode End Sub 

I did a timing test of Freezing using .Select vs .Activate. Here is the code

Dim numLoops As Long Dim StartTime, LoopTime As Long numLoops = 1000 Debug.Print ("Timing test of numloops:" & numLoops) StartTime = Timer For I = 0 To numLoops targetSheet.Activate With ActiveWindow If .FreezePanes Then .FreezePanes = False .SplitColumn = 2 .SplitRow = 1 .FreezePanes = True End With Next I LoopTime = Timer Debug.Print ("Total time of activate method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss")) StartTime = Timer For I = 0 To numLoops targetSheet.Select Application.Range("C2").Select Application.ActiveWindow.FreezePanes = True Next I LoopTime = Timer Debug.Print ("Total time of select method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss")) 

And here are the results.

Timing test of numloops:1000 Total time of activate method:00:00:39 Total time of select method:00:00:01 

As you can see, .Select is much faster.