Freeze pane

[Joe]

Hi,

How can VBA be coded to specify a particular row that can
freeze pane? also, how can using the ".clear" method code
that will delete the unwanted cell(in row) that is not
required?

regards,
Joe

 

[Norman Jones]

Hi Joe,

To split the active workbook at (say) row 2, try:

Sub Tester()

With ActiveWindow
.SplitColumn = 0
.SplitRow = 2
.FreezePanes = True
End With
End Sub

Adjust the row (and column) number to suit.

To remove the split ,

Sub Unsplit()

With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
End Sub

To delete the contents of a cell:

Range("A2").ClearContents

To delete the contents of a cell and remove its formatting:

Range("A2").Clear

 

[Tom Ogilvy]

Just some added info:
Freezepanes and split are two different approaches to a similar problem.
While Norman's code does accomplishe the intended purpose, if you then
unfreeze the window, you see that a split is also applied (freezepanes
appears to take precedence). Another way to apply a freezepane at row 2
without doing a split would be to select either row(3) or cell A3

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

and to unfreeze

With ActiveWindow
.FreezePanes = False
End With