Vba to hide & unhide ranges with scrollbar

QuestionEdit

QUESTION: I need advice on vba coding. Upon opening my workbook, I only want one range of data to be displayed ("A9:T11"). The vba coding I need should not affect rows 1 thru 8, containing headings. Range A13:T158 should be hidden. When I move the scrollbar down 3 steps, the present range must be hidden, the next range visible and the other ranges hidden as well. When the scrollbar is moved upwards 3 steps, the present range must be hidden, the previous range unhidden, the remaining ranges also hidden, regardles of where in the range A9:T158 I am. I include part of my coding, which manually does the job. Is there a way to automate it, thus shortening my code ? Sub Macro1() Range("A12:T158").Select Selection.EntireRow.Hidden = True ActiveWindow.SmallScroll Down := 3 Range("A12:T14").Select Selection.EntireRow.Hidden = False Range("A9:T11").Select Selection.EntireRow.Hidden = True Range("A15:T158").Select Selection.EntireRow.Hidden = True

'(Some more coding to hide & unhide ranges)

End Sub

This works, but could be shortened, I hope.

I would appreciate it if you could show me how. ANSWER: Ivan I am sure we can make your code shorter and more efficient; but, I need to know a little more about the logic that determines what rows are visible. Can you explain what rows you want to have visible and what determines the rows you want to have visible? It seems like the code you are describing will behave differently depending on one's screen resolution and other factors. If you do one or two Small Scroll Downs, what if anything is supposed to happen? ---------- FOLLOW-UP ----------

QUESTION: Thanks for the prompt reply. I hope that my following explanation will shed more light on what I'm trying to achieve.

Each range consist of three rows. The first range (A9:T11) should be the only range visible when the workbook is opened, as well as the headings (rows 1 thru 8 - these panes have been "frozen" at row 9). Every time I go down three rows, the current range (A9:T11) must be hidden, the next one visible (A12:T14) and all the other ranges (A15:T158)hidden. Data can only be entered in the third row of each range, in column C (which is a merged cell stretching from column C thru column T. Once data was entered in this cell (C11) and after either the enter key was hit or the scroll bar clicked, the actions I described in my question should take place. I've included the workbook as a jpeg image named Comments.jpg Rename the extension to .xls to view the workbook.

AnswerEdit

Ivan

If you put this in the thisworkbook code module, when the file opens it will display only rows 1 through 11.

Private Sub Workbook_Open()

   Range("A12:T158").Select
   Selection.EntireRow.Hidden = True
   Range("A9:T11").Select
   Selection.EntireRow.Hidden = False

End Sub

If you put this code in the code module for sheet1 (or the sheet where your data is) it will hide the current group of 3 rows and un-hide the next group, select column A and th third row of the group.

Private Sub Worksheet_Change(ByVal Target As Range)

   ActiveCell.Offset(-2, 0).Resize(3, 1).EntireRow.Hidden = True
   ActiveCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False
   ActiveCell.Offset(3, 0).Activate

End Sub

I have assumed you can only change the third row in each group, the merged cell, but, the code will run anytime the worksheet is changed. This doesn't do anything for you as far as moving up the sheet or restoring the visibility of all the rows if you ever want to do that. Hopefully the syntax above for Offseting and resizing will get you going in the right direction. It can definitely be better than the brute force coding method you were starting with; but, it will probably require a little more thought to make it work correctly for you.

Good luck.