Is there any way to freeze the top row by default or otherwise save the state of frozen cells?

Here's a macro I use to freeze the top row, apply the filter, and autowidth columns to contents to a maximum of 40 pixels wide. Assign it to a button in the ribbon or QAT.

Sub FreezeColumnFilter()

Dim r As Range

Dim mCell As Range

Set r = ActiveCell

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False


With ActiveWindow

.FreezePanes = False

.ScrollRow = 1

.ScrollColumn = 1

.FreezePanes = True

.ScrollRow = r.Row

End With


With ActiveSheet

.AutoFilterMode = False


End With

For Each mCell In ActiveSheet.UsedRange.Rows(1).Cells


If mCell.EntireColumn.ColumnWidth > 40 Then _

mCell.EntireColumn.ColumnWidth = 40

Next mCell

End Sub

/r/excel Thread