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

How to freeze multiple rows and columns in excel?

提问by user206168

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?

那可能吗？

回答by daniellopez46

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 被隐藏或使用 View>New Window 然后Arrange All。

回答by Pat Cruz

Range("A1").Select

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

You can play with split column and rows.

您可以使用拆分列和行。

回答by Our Man in Bananas

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

回答by Paul Cook

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 中是可能的。您可以选择任何单元格，转到窗口选项卡和冻结窗格。那个牢房左边和上面的所有东西都被冻结了。但微软似乎决心在每个新版本的 Office 中删除更多功能。每个都有更少的我们知道和喜爱的旧功能。很快，您不妨使用 Works 或 Open Office。我希望我可以切换到 Word Perfect，但是太多的公司在使用 MS Office。

回答by Marcucciboy2

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:

我知道这个问题很旧，但我经常访问它，以至于我想我会添加@daniellopez46 答案的 VBA 版本。此代码将：

1. Create a second window of your spreadsheet
2. Tile the windows vertically (side by side)
3. Show a range starting at column Son one window
4. Scroll to column Zonward on the second window
5. Freeze the top 2 rows of the second window

1. 创建电子表格的第二个窗口
2. 垂直平铺窗户（并排）
3. 显示从S一个窗口的列开始的范围
4. Z在第二个窗口向前滚动到列
5. 冻结第二个窗口的前两行

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 ResetWindowmacro.

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