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

Question

I am looking to programmatically freeze the top row of an Excel worksheet from VBA. The end goal is to produce the same effect as the View > Freeze Panes > Freeze Top Row command in Excel 2007 so that the top row of the worksheet is frozen and users can see the top row of the worksheet even as they scroll through the data.

Answer 1

Rows("1:1").Select
ActiveWindow.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.

Answer 2

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 = 1
End With
ActiveWindow.FreezePanes = True