Excel Freezing too many rows

Question

I'm trying to freeze the top 4 rows in my Excel worksheet, but it's freezing 20 rows. I'm using the following VBA code to freeze the rows:

Range("1:4").Select
With ActiveWindow
    .FreezePanes = True
End With

This is a screenshot, with added notiation show what I want, and what is actually happening. I scrolled a bit to show the freeze, and you can see that row 20 jumps to 36. It looks like there's a vertical freeze line between G and H, but it's not important (that could be the right-margin page break).

Any help is appreciated. Thanks.

`ActiveWindow.FreezePanes = True` will freeze from the active cell. Try `Range("A5").Select` first — lebelinoz, Jun 13 '17 at 21:13
@lebelinoz Like Jeeped's answer, this has set the freeze line right after Row 13. — LazyBear, Jun 13 '17 at 21:38
It's to do with the location of `ActiveCell`, which may not necessarily appear in a selected range like in the answer given by @Jeeped. See my answer below — lebelinoz, Jun 13 '17 at 21:44

score 7 · Accepted Answer · 2017-06-13T21:43:33.757

7

If you want to freeze the top 4 rows, reference the 5th row.

Range("5:5").Select
With ActiveWindow
    .FreezePanes = True
End With

See excel vba freeze pane without select and How to avoid using Select in Excel VBA macros.

Without .Select.

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

edited Jun 13 '17 at 21:43

answered Jun 13 '17 at 21:11

Using your code, the freeze line is after row 13. I'll take a look at the links you posted. Thx – LazyBear Jun 13 '17 at 21:35
No merged cells. – LazyBear Jun 13 '17 at 21:43
That did it. Using the `Select` method was the issue. Thanks! – LazyBear Jun 13 '17 at 21:47

score 2 · Answer 2 · answered Jun 13 '17 at 21:42

2

It's to do with the active cell, which may not necessarily appear in the selected range. Try:

Range("A5").Activate
With ActiveWindow
    .FreezePanes = True
End With

answered Jun 13 '17 at 21:42

lebelinoz

4,890
10
33
56

TaitK · Answer 3 · 2018-02-07T22:08:21.127

Also, this may affect where your split occurs

The SplitColumn/SplitRow values represent the last cell above/left of the split in relation to the top-left cell that is visible. 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.

(https://stackoverflow.com/a/48672141/9329538). Please note that manually scrolling just before the .split code runs can also cause this.

Excel Freezing too many rows

3 Answers3