dynamically freezing panes in excel

excel

Solution

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

Problem

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

Original source