如何在excel中冻结多行和多列？

I want to freeze the range S1:Y17, hide the columns A:R, and from column Z onward I only want to freeze the top 2 rows.

我想冻结范围S1:Y17,隐藏列A:R,并从列Z开始,我只想冻结前2行。

Is that possible?

那可能吗?

5 个解决方案

#1

Range("A1").Select

With ActiveWindow
    .SplitColumn = 1
    .SplitRow = 1
    .FreezePanes = True
End With

You can play with split column and rows.

您可以使用拆分列和行。

#2

There is no way to accomplish this using any of the options under any of the ribbons.

使用任何色带下的任何选项都无法实现此目的。

Alternatively you can set your freeze point at Z18, especially since columns A:R are hidden or use View>New Window and then Arrange All.

或者,您可以将冻结点设置为Z18,尤其是因为列A:R被隐藏或使用“视图”>“新建窗口”然后使用“全部排列”。

#3

sure just select a cell Z3, and on the Window menu click Freeze Panes

确保只选择一个单元格Z3,然后在“窗口”菜单上单击“冻结窗格”

and in VBA, try this:

在VBA中,试试这个:

Range("Z3").select
ActiveWindow.FreezePanes = True

#4

This was possible in older versions of excel. You could select any cell, go to the windows tab and the Freeze Panes. Everything to the left and above that cell was frozen. But Microsoft seems determined to remove more functionality with each new version of Office. Each has fewer of the old functions we knew and loved. Soon, you might as well use Works, or Open Office. I wish I could switch to Word Perfect, but too many companies are using MS Office.

这在旧版本的excel中是可能的。您可以选择任何单元格,转到Windows选项卡和冻结窗格。该单元格左侧和上方的所有内容都被冻结。但微软似乎决定在每个新版本的Office中删除更多功能。每个人都知道和喜爱的旧功能较少。很快,你不妨使用Works或Open Office。我希望我可以切换到Word Perfect,但有太多公司正在使用MS Office。

#5

I know this question is old but I visit it often enough that I thought I would add a VBA version of @daniellopez46's answer. This code will:

我知道这个问题很老但我经常访问它,我想我会添加一个VBA版本的@ daniellopez46的答案。此代码将:

Create a second window of your spreadsheet

创建电子表格的第二个窗口

Tile the windows vertically (side by side)

垂直平铺窗户(并排)

Show a range starting at column S on one window

在一个窗口中显示从S列开始的范围

Scroll to column Z onward on the second window

在第二个窗口中向前滚动到Z列

Freeze the top 2 rows of the second window

冻结第二个窗口的前2行

Once you are finished working on the spreadsheet and close one of the windows you may not want to keep the formatting that was done, so I included a ResetWindow macro.

完成电子表格并关闭其中一个窗口后,您可能不想保留已完成的格式,因此我包含了一个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

If you would like code that hides the ranges you're not using instead of simply scrolling over to where you want to work, I made the next snippet as well to hide all but the ranges you're working with.

如果您希望代码隐藏您不使用的范围而不是简单地滚动到您想要工作的位置,我也会创建下一个片段,以隐藏除您正在使用的范围之外的所有范围。

It also has its own ResetWindow for when you're done working with both windows and want to close and save the document.

它还有自己的ResetWindow,当你完成两个窗口的工作并想要关闭并保存文档时。

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

#1

Range("A1").Select

With ActiveWindow
    .SplitColumn = 1
    .SplitRow = 1
    .FreezePanes = True
End With