make next row unhide, only after 1st row occupied

[draddy]

So to try and explain, I've created a user input sheet that I would like to contain an unlimited number of line items.

That way, the end user could either enter in 1, or 1000 line items, without having to see 1000 lines to be filled from the start.


That way, as soon as the user inputs the first set of data in the first row, the second row appears to input the second set. (perhaps starting with 15, and then counting up one at a time from there).

Is this possible? This data will be referenced in miles and miles of VB code, so essentially the 1000 different rows will need to be hidden, than unhidden one at a time, as the row above has data entered into it.


Thanks for the help.

[ewhaley50]

Without seeing the file i can think of one way that might work, you can detect if a file is ever changed in a given range if it is then set what rows or coloumns you want to hidden or not hidden.

The code below if you have the switch statement inside the If not intersect you should be able to achive this.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
'this dectects if anything changed in a1-a1000
end if


'the switch can be used to determine what needs to be hidden or not
        Select Case Range("A10")
        Case "Item 1"
           
            Range("11:40").EntireRow.Hidden = False
            Range("41:1157").EntireRow.Hidden = True
            
        Case "next item"
end Select
end Sub

[draddy]

Thank you for that response, it was incredibly helpful. I was able to take that code, modify it some and get it to work just as I was requesting.


However, it wasn't as well recieved as I had hoped. As an alternative, I'd like to try this.




Is this possible:

Setup a macro to ask "how many rows would you like to see?" and have the macro unhide that many rows, and leave the remaining rows hidden?

Seems simple, but I can't figure out what code to use to make it reference the cell. (essentially the question will return a number into a cell, and the macro will have to reference that cell as to how many rows to unhide).


Thanks again.
-Draddy