我希望冻结范围S1:Y17
,隐藏列A:R
,并且从列Z开始,我只想冻结前2行。
这可行吗?
Range("A1").Select
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
.FreezePanes = True
End With
.SplitRow = 0
设置为意味着不会冻结任何行,同样地,将 .SplitColumn = 0
设置为意味着不会冻结任何列。 - Marcucciboy2无论在任何选项卡下都不能完成此操作。
或者,您可以将冻结点设置为Z18,特别是因为A:R列被隐藏了,或使用“视图>新建窗口”,然后排列所有窗口。
请在单元格Z3上点击窗口菜单中的冻结窗格
在VBA中,尝试以下操作:
Range("Z3").select
ActiveWindow.FreezePanes = True
我知道这个问题很老了,但我经常访问它,所以我想添加@daniellopez46答案的VBA版本。 这段代码将:
S
开始的范围Z
一旦您完成电子表格的工作并关闭其中一个窗口,您可能不想保留所做的格式,因此我包括了一个ResetWindow
宏。
Sub MacroA()
Dim window1 As Window
Set window1 = ActiveWindow
ResetWindowA
Dim window2 As Window
Set window2 = window1.NewWindow
Windows.Arrange xlArrangeStyleVertical
With window2
'jumps to column S
.ScrollRow = 1
.ScrollColumn = 19
End With
With window1
'jumps to column Z
.ScrollRow = 1
.ScrollColumn = 26
'freezes the first two rows
.SplitRow = 2
.SplitColumn = 0
.FreezePanes = True
End With
End Sub
Sub ResetWindowA()
With ActiveWindow
'reset previous freeze, if any
.FreezePanes = False
.SplitRow = 0
.SplitColumn = 0
End With
End Sub
Sub MacroB()
Dim window1 As Window
Set window1 = ActiveWindow
ResetWindowB
Dim window2 As Window
Set window2 = window1.NewWindow
Windows.Arrange xlArrangeStyleVertical
With window2
.ScrollRow = 1
.ScrollColumn = 1
'Hide all but S1:Y17
Columns("A:R").EntireColumn.Hidden = True
Columns("Z:XFD").EntireColumn.Hidden = True
Rows(18 & ":" & Rows.Count).EntireRow.Hidden = True
End With
With window1
.ScrollRow = 1
.ScrollColumn = 1
'Hide all columns before Z
Columns("A:Y").EntireColumn.Hidden = True
'freezes the first two rows
.SplitRow = 2
.SplitColumn = 0
.FreezePanes = True
End With
End Sub
Sub ResetWindowB()
'unhide rows
If Columns("XFD").EntireColumn.Hidden = True Then
Columns("A:R").EntireColumn.Hidden = False
Columns("Z:XFD").EntireColumn.Hidden = False
Rows(18 & ":" & Rows.Count).EntireRow.Hidden = False
Else
Columns("A:Y").EntireColumn.Hidden = False
End If
With ActiveWindow
'reset previous freeze, if any
.FreezePanes = False
.SplitRow = 0
.SplitColumn = 0
.ScrollRow = 1
.ScrollColumn = 1
End With
End Sub