CODEWITHSUNDEEP
excel
masfenix
masfenix

Reputation: 7996

dynamically freezing panes in 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

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.

Upvotes: 6

Views: 26358

Answers (2)

Patman
Patman

Reputation: 95

This may be a bit late but I was looking for a solution like this and I figured out a better way since unfreezing and re-freezing can be quite jumpy. Instead try grouping: http://office.microsoft.com/en-us/excel-help/outline-group-data-in-a-worksheet-HA010095247.aspx

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 3

Related Questions