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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,679
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

Code:
Sub ttt()
Application.EnableEvents = True
End Sub
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,679
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you JLGwhiz, this is working just great :)

Thanks so much for your help

Tony
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
P.S.

You should change this line

Target.EntireRow.Delete xlShiftUp

to this

Code:
Target.EntireRow.Hidden = True
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,679
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi GWhiz,
Thanks I saw that and changed it anyway,

But thanks for pointing that out.

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top