Scroll then lock row

Question

I have a worksheet that has header data in the first few rows, followed by a table with column headers. The header data includes parameters that are used to calculate values in the table.

What I want is to allow the user to scroll the sheet so that the header data is no longer visible but then freeze the table heading row when it becomes the first row. Then if they need to change a parameter, they can scroll back up and the top few rows come back into view.

I thought this was fairly easy to do but I can't remember how to do it. The only option I can find is the Freeze Panes option, but that freezes everything and I only want it to freeze when the header row is at the top.

Is this even possible?

Edited - To add a little more clarification, the freeze panes option freezes everything above the selected row (unless I have missed it), which is not what I am looking for. I want the sheet to behave similar to a website where there may be an ad banner at the top, followed by a navigation bar/logo/etc., then the content. As you scroll down, the ad banner moves out of view but when the nav bar is at the top, it stops and remains in view while the content continues to scroll.

Let's say I have the first 5 rows that have cells to capture input values. Then row 6 is a table heading and rows 7-100 are the table data. I want the user to be able to hit the scroll down button 5 times to move past the input values. Then I want row 6 to freeze at the top so that as the user continues scrolling, the table heading is visible. Conversely, when the user scrolls up, row 6 remains at the top until the user gets to row 7, then if they continue scrolling, the input values move into view.

Excel does something similar if you use the "format as table" function and indicate that the selection has a header row. Then when scrolling, the column headers will replace the "A", "B", "C", etc. address labels. This won't work directly for me because the table structure is a little more complex.

See [here](http://stackoverflow.com/questions/3232920/how-can-i-programmatically-freeze-the-top-row-of-an-excel-worksheet-in-excel-200). — Scott Craner, Nov 18 '15 at 22:20
not sure why you added vba and the answers also suggest a vba option. just select the row/colum (either or both) and then the option freeze panes — davejal, Nov 18 '15 at 23:39
Thanks Scott, but from what I can tell, that thread provides a solution to freeze the row, but not dynamically. I updated the post with more information and in the example, I only want row 6 to freeze when it is the top row of the sheet. I'm still new to VBA and I suppose I could write a check to see if row 6 is the first row and then use the freeze code from the other thread, but that seems inefficient. — Brian, Nov 19 '15 at 16:50

score 0 · Answer 1 · answered Nov 18 '15 at 22:34

Use this in the worksheet's code page (right-click worksheet name tab, View Code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim iVISROWS As Long
    Dim iHDRROWS As Long
    iVISROWS = 5
    iHDRROWS = 3
    Cells(1, 1).Resize(iHDRROWS, 1).EntireRow.Hidden = _
       Target(Target.Rows.Count).Row > iVISROWS
End Sub

Optionally, take out the declaration and assignment of iVISROWS and iHDRROWS and out the following into the declaration area (top) of a module code sheet.

Option Explicit

Public Const iVISROWS As Long = 5
Public Const iHDRROWS As Long = 3

Play with the number of header rows (iHDRROWS) and the bottom of the current selection that triggers hiding the header rows (iVISROWS) untilo you get the results you are looking for.

Thanks Jeeped! This is almost exactly what I was looking for. By freezing the header row and adding your code, it achieves the desired result with the only caveat that the active cell has to be below iVISROWS, meaning the up/down arrows work great but not the scroll bar/buttons. Good stuff and I'm going to keep playing around with it. — Brian, Nov 19 '15 at 17:01

score 0 · Accepted Answer · answered Nov 18 '15 at 23:16

As far as i understood you want to keep visible a row or column thats not first. Which is kinda exotic.

If you want to hide configureables to save visual space and keep headers at all times:

Simply freeze as many rows/columns as you need for both your configs and headers. And then HIDE the rows/columns you won't want always in view.

You can even have a simple button that does this hiding and unhiding via one click:

Sub transaction1()
    Rows("10:50").Hidden = Not Rows("10:50").Hidden
End Sub

As for the freeze panes, in case someone doesn't know you can freeze as many as you want. If you want to freeze columns A and B and rows 1 and 2 simply click on C3 and then use the freeze function. If you want to freeze 10 rows only - click on A11 and then use the freeze.

Though i usually just offload my service space to elsewhere and quickly jump to and from it with =HYPERLINK()

In any event your required result isn't any more convenient than the other ways you can achieve navigational ease. So if I've scrolled to row 14000 if i want to scroll back to the beginning to view my settings i still have to go through 14k rows. Having an always frozen settings area as well as headers is quicker to navigate. And if your settings area takes that much space either move it and create more compact navigations for it or simply hide/show it on demand.

There is no doubt you can have even more exotic ways to achieve the effect, but is it mandatory to do it that way?

Thanks Helena! Your answer gave me a different perspective that I hadn't thought about. Jeeped's answer above is what I had (mostly) envisioned but you bring up a great point about having to scroll back to the top to see the settings. I also have buttons that will be useful to be able to see the active row and the button at the same time, so your solution of using a button to hide the settings/button area is a better design. Thank you!!! — Brian, Nov 19 '15 at 17:06
There is an even larger benefit to frozen navigation. If you have the first 10 rows frozen and scroll to row 10000, Clicking a hyperlink in the frozen A3 that takes you to another sheet KEEPS your scrolling position when you come back at row 10000. Hyperlink nav is real nice. I have a whole hub page for it. — helena4, Nov 19 '15 at 17:58

score 0 · Answer 3 · answered Dec 05 '17 at 08:43

I had the same problem and have much less tech savvy to do all the coding above. My workaround is very simple. Insert a new row right at the top of the sheet. Copy the headings of the table to the new row 1 and then freeze the top row as normal. As you scroll the top dummy row stays above your table and the rest disapears. Your original headings still stay in place for filtering as needed if you scroll up.

Scroll then lock row

3 Answers3