动态冻结excel中的窗格 [英] dynamically freezing panes in excel

问题描述

我有一个Excel表格，其中的数据格式如下:

I have an excel sheet with data in the following format:

Title
Summary A
A info 1
A info 2
A info 3
Summary B
B info 1
B info 2
Summary C

所以现在我在Excel中只有"title"作为冻结的窗格.它可以正常工作，并且所有数据都很多，因此当您位于工作表中间时，很难知道您是在summary A还是summary B中工作.

so right now I have only "title" as a frozen pane in excel. It works fine and all but there is a lot of data so when you are in the middle of the sheet its hard to know whether you are working in summary A or summary B.

有人知道如何动态冻结窗格吗?即，最初title和Summary A将被冻结.然后，当用户向下滚动直到下一部分时，Title和Summary B将被冻结，依此类推.

Does anyone know how to freeze panes dynamically? ie, initially title and Summary A will be frozen. Then as the user scrolls down until the next section then Title and Summary B will be frozen and so on.

推荐答案

您可以尝试一下(在工作表代码模块中).每次选择更改时，它都会在第一列中向上检查"Summary *"之类的内容:如果工作表尚未冻结到该行，它将进行调整.

You can try this out (in the worksheet code module). Each time the selection changes, it checks upward in the first column for content like "Summary*": if the worksheet isn't already frozen to that row, it will make that adjustment.

一个困难是要向上滚动，您必须单击顶部窗格中的一行...

One difficulty is that to scroll up you have to click on one of the rows in the top pane...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static lastFreeze As Long
    Dim c As Range, r As Long, sel As Range

    Set sel = Selection
    'get the first cell on the selected row, then
    '  go up until find a content like "Summary*"
    Set c = Target.Parent.Cells(Target.Cells(1).Row, 1)
    Do While (Not c.Value Like "Summary*") And c.Row > 1
        Set c = c.Offset(-1, 0)
    Loop

    'need to switch freeze location?
    If c.Row > 1 And c.Row <> lastFreeze Then
        ActiveWindow.FreezePanes = False
        'prevent re-triggering event
        Application.EnableEvents = False
        Application.GoTo c.Offset(-1, 0), True
        c.Offset(1, 0).Select
        ActiveWindow.FreezePanes = True
        sel.Select
        Application.EnableEvents = True
        lastFreeze = c.Row
    End If

End Sub

这篇关于动态冻结excel中的窗格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！