Can you Freeze Panes on one row and have it scroll to the top and stop?

[USAMax]

I expect someone to come back and just say no, it cannot be done. At least I can stop looking for it.

I have to keep row 16 on the screen at all times but I need more real estate on the screen when I scroll down. What I want is to have row 16 scroll up until it gets to the top of the screen as if it were row one with the freeze pane on and all the rows below it would scroll up keeping row 16 at the top.

I am currently using Worksheet Change so I would not like to use this function. Any other ideas?

My thought is to hide and unhide rows 1 through 15 as I scroll up and down but how?

I have Excel 2010.

 

[Trevor G]

If you convert your table to a Data Table from the Insert Tab next to Pivot Tables or use Ctrl + T then when you scroll through the table the first row in the table will replace the Column Headings which then keeps them in view.

 

[USAMax]

Thank you Trevor,

This sounds like a very good ides. I don't think the client will go for it but I will not know until I ask them but at least I can give them options.

Thank you again.

 

[Rick Rothstein]

I expect someone to come back and just say no, it cannot be done. At least I can stop looking for it.

I have to keep row 16 on the screen at all times but I need more real estate on the screen when I scroll down. What I want is to have row 16 scroll up until it gets to the top of the screen as if it were row one with the freeze pane on and all the rows below it would scroll up keeping row 16 at the top.

Not sure how the Change event fits into this, but for what you seem to have asked above, try this... scroll the sheet so that Row 16 is at the top, then select cell A17 and then apply the Freeze Pane to it... Row 16 should now remain as the displayed frozen top pane while you scroll the lower pane.

 

[USAMax]

Hey Rick, good to hear from you again!

That is exactly what I want to do but I need to do it dynamically. I need rows 1 to 15 to print and I need the user to be able to use the up arrow and up scroll to see above row 16 too.

Thanks,

 

[Rick Rothstein]

Hey Rick, good to hear from you again!

That is exactly what I want to do but I need to do it dynamically. I need rows 1 to 15 to print and I need the user to be able to use the up arrow and up scroll to see above row 16 too.

I'm still not clear on the "dynamic" part, but maybe instead of Freezing Panes, you want to use Split Panes instead (then each pane gets it own vertical scroll bar). Scroll Row 16 to the top, select cell A17 and press ALT+ws (the keyboard shortcut so you don't have to try and locate it on the Ribbon) to place the split bar.

 

[USAMax]

My current screen shows 25 rows because two of the row heights are quite large in the headers. I was thinking that if the user scrolled or selected the 26th row, row 1 would be hidden. This would need to happen for the first 15 rows. When row 41is selected the Freeze Panes would be activated on row 16 as you suggested. This would not be a Worksheet_Change event but if there is such a thing as a Worksheet_Select event or a Worksheet_Scroll event then this would work.

 

[Rick Rothstein]

My current screen shows 25 rows because two of the row heights are quite large in the headers. I was thinking that if the user scrolled or selected the 26th row, row 1 would be hidden. This would need to happen for the first 15 rows. When row 41is selected the Freeze Panes would be activated on row 16 as you suggested. This would not be a Worksheet_Change event but if there is such a thing as a Worksheet_Select event or a Worksheet_Scroll event then this would work.

If I understand your request correctly (split screen only when Row 16 is exceeded as the top row), I do not think there is a way to do it as there is no "scroll sheet" event from which to monitor the currently scrolled top row.

 

[USAMax]

Thanks Rick,

Sometimes a hearing that it cannot be done is as good as giving the answer. I'll keep thinking about it but I won't keep looking for a way to do it. With some of the answers you gave me, I know if you cannot do it, it cannot be done using VBA.