Hide/unhide rows as data gets added in rows above?

[tonywatsonhelp]

Hi Everyone,

I thought this would be easy but struggling as I've got set ranges I want this to work in.

So lets explain the concept first,

I have 4 areas in my document that people fill out, to save space on the sheet I hide the rows not being used as there is a lot of row that will be blank,

for example F10-F20 are for out of stock Items, now I might not have any out of stock item's but I'm likely to have some, I've look at the past and never had more than 10 so I dedicate 10 Rows to out of stock items,
below that I have Back orders,

Now Back orders can be a lot so I've given 20 Rows to that F25-F45.

Now if you can picture the sheet without any Out of stock Items or Back orders I have Rows F10 - F45 taking up room so you have to scroll down to them and below, but I don't need empty rows show so I hide F11-F20 and F26-F45 etc. so now I have a much more condensed page, with just one row on show for Out of Stock and one for Back orders,

so I need a way to hide / unhide these hidden rows if data is added?
so if F10 is on show if data is added to F10 the F11 unhides F11 then F12 etc, however if I delete the data in f12 I want the row to hide again.

heres a bit more detail.

So I have 4 ranges all in the same column "F"

they are F10-F20, F25-F45,F51-F56, F70-F99.

now each of these ranges starts of as a hidden except the first row so F10,F25,F51,F70 are all unhidden, the rest are hidden.

all I want is a macro that when you add data into the row above in the range it unhides the row below,

So F10 is empty, if I add data into F10, F11 becomes available down to F19 with F20 being the last one, however if you delete the data from the cell the cell you deleted from hides except F10 etc that are always visible,

please help

Tony

 

[JLGWhiz]

The first code is event code and is to be copied and pasted into the worksheet where your hidden rows reside. The second code is a tool that should be copied and pasted into the standard code module 1.
Make sure your workbook is a macro enabled workbook and that you save the file after you install the code. The event code will run when any changes occur in column F in the ranges you have specified in the OP. The second code will have to be manually run any time your event trigger becomes disabled and your event code will not run. First check to be sure you are not in design mode. If you are not in design mode and the code still will not run when changes are made in the designated ranges, then run the snippet to reset the event trigger.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim rng As Range, i As Long, x As Long, y As Long
Set rng = Union(Range("F10:F20"), Range("F25:F45"), Range("F51:F56"), Range("F70:F99"))
    If Not Intersect(rng, Target) Is Nothing Then
        If Target.Value <> "" Then
            Target.Offset(1).EntireRow.Hidden = False
        ElseIf Target.Value = "" And Target.Row <> 11 And Target.Row <> 25 And Target.Row <> 51 And Target.Row <> 70 Then
            Target.EntireRow.Delete xlShiftUp
        ElseIf Target.Row = 10 Or Target.Row = 25 Or Target.Row = 51 Or Target.Row = 70 Then
            If Target.Value = "" Then
                Select Case Target.Row
                    Case 10
                        x = 11: y = 20
                    Case 25
                        x = 26: y = 45
                    Case 51
                        x = 52: y = 56
                    Case 70
                        x = 71: y = 99
                End Select
                For i = x To y
                    If Cells(i, "F") = "" Then
                        Rows(i).Hidden = True
                        Exit For
                    End If
                Next
            End If
        End If
    End If
Application.EnableEvents = True
End Sub

Sub ttt()
Application.EnableEvents = True
End Sub

 

[tonywatsonhelp]

Thank you JLGwhiz, this is working just great

Thanks so much for your help

Tony

 

[JLGWhiz]

Thank you JLGwhiz, this is working just great

Thanks so much for your help

Tony

You're welcome,
regards, JLG

 

[JLGWhiz]

P.S.

You should change this line

Target.EntireRow.Delete xlShiftUp

to this

Target.EntireRow.Hidden = True

 

[tonywatsonhelp]

Hi GWhiz,
Thanks I saw that and changed it anyway,

But thanks for pointing that out.

Tony