Hide and unhided based on the above cell value

[cairo95]

Hello,

I am working on an excel spreadsheet that i would like to automatically unhide the row below if text is entered in the previous row (specific cell). i have up to 12 rows max that i need to unhide when there is a value in the previous row (specified cell). they would unhide as text is placed in the previous specified cell. for example. row 1 would always be visible and when text is entered in the specified cell in row 1, row 2 would unhide and if row 2 (specified cell) has text entered, row 3 will unhide and so on... up to a max of 12 rows....and if the text is removed the row below will hide.

I appreciate any help!

thanks in advance!

Cairo95

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = [COLOR=#ff0000]5[/COLOR] Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
   End If
End Sub

This is based on a value in col E, change the value in red to suit.
The code needs to go in the relevant sheet module

 

[cairo95]

Thank you Fluff,

this works perfect for the unhide but when i clear the cells they dont auto hide. Is this even possible with Excel?

thanks again for your help!
Half way there

Be
Cairo95

 

[Fluff]

How are you clearing the cells?
If you select a cell & press "Delete" the row below will hide

 

[cairo95]

i failed to mention the cells starting from column 2 are merged columns B:G...does that make a difference?

thank again!

Regards,

Cairo95

 

[cairo95]

I tried what you recommended and still does not hide the row when i hit delete.


Regards,

Cairo95

 

[Fluff]

Very probably. Merged cells can be major problem & I avoid them like the plague.
What column are you putting the values in?

 

[cairo95]

Here is a snapshot of my spreadsheet:

starting on column B row 53 down to row 64...is there anyway you can restrict the code to the below cell references? B53-B64

Once again thank you!
Cairo95

 

[Fluff]

This will restrict it to look at B53:B64

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B53:B64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
   End If
End Sub

But the merge cells will still cause problems.

 

[cairo95]

thank you, as soon as i unmerged the cells it worked perfectly! i will have to find away to create my sheet without the merged cells.

thank you so much for your help!!

Best Regards,

Cairo95