Dynamically freezing glass in excel

Question

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 only have a "title" as a frozen panel in excel. It works fine, and that's all but a lot of data, so when you're in the middle of a worksheet it's hard to see if you're working in summary A or summary B

Does anyone know how to dynamically freeze panels? those. initially title and summary A will be frozen. Then, when the user scrolls to the next section, title and summary B will be frozen, etc.

Answer 1

You can try this (in the worksheet code module). Each time the selection changes, it checks up in the first column for content such as "Summary *": if the worksheet is not already frozen on this row, it will perform this setting.

One of the difficulties is that to scroll, you need to click on one of the lines in the top panel ...

 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 

Answer 2

It may be a little late, but I was looking for such a solution, and I realized that it is better, since thawing and re-freezing can be quite nervous. Try grouping instead: http://office.microsoft.com/en-us/excel-help/outline-group-data-in-a-worksheet-HA010095247.aspx