How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA? How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA? vba vba

How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA?


Rows("2:2").SelectActiveWindow.FreezePanes = True

Select a different range for a different effect, much the same way you would do manually. The "Freeze Top Row" really just is a shortcut new in Excel 2007 (and up), it contains no added functionality compared to earlier versions of Excel.


Tomalak already gave you a correct answer, but I would like to add that most of the times when you would like to know the VBA code needed to do a certain action in the user interface it is a good idea to record a macro.

In this case click Record Macro on the developer tab of the Ribbon, freeze the top row and then stop recording. Excel will have the following macro recorded for you which also does the job:

With ActiveWindow    .SplitColumn = 0    .SplitRow = 1End WithActiveWindow.FreezePanes = True


The problem with the recorded macro is the same as the problem with the built-in action: Excel chooses to freeze the top visible row, rather than the actual top row where the header information can be found.

The purpose of a macro in this case is to freeze the actual top row. When I am viewing row #405592 and I need to check the header for the column (because I forgot to freeze rows when I opened the file), I have to scroll to the top, freeze the top row, then find my way back to row #405592 again. Since I believe this is stupid behavior, I want a macro to correct it, but, like I said, the recorded macro just mimics the same stupid behavior.

I am using Office 2011 for Mac OS X Lion

Update (2 minutes later):

I found a solution here: http://www.ozgrid.com/forum/showthread.php?t=19692

Dim r As Range Set r = ActiveCell Range("A2").Select With ActiveWindow     .FreezePanes = False     .ScrollRow = 1     .ScrollColumn = 1     .FreezePanes = True     .ScrollRow = r.Row End With r.Select