Hide rows automatically when another cell contains specific texts

[Steve1208]

Excel version 2013 on windows.

I have a spread sheet that I need to hide entire rows based on another cells contents.
I need two rules, based on different conditions.

Rule 1

When cell C14 contains "Select"

I need Rows 20:34 to be hidden.

When Cell C14 contains Anything other than "select"

I need Rows 20:34 to be unhidden.


Rule 2

Between the rows 40:54 I only want the next row below the current populated row to be unhidden at a time.

For example when cell C40 is populated
only rows 40:41 should be shown. Then rows 55 and below, 39 and above should be unhidden as normal, but rows 20:34, should still adhere to the 1st rule mentioned above.

When cell C41 is populated
rows 40:42 should be shown. Then rows 55 and below 39 and above should be unhidden as normal, but rows 20:34, should still adhere to the 1st rule mentioned above.

When cell C42 is populated
rows 40:43 should be shown. Then rows 55 and below 39 and above should be unhidden as normal, but rows 20:34, should still adhere to the 1st rule mentioned above.


Thank you in advance.
Please let me know if more information is required.

 

[SteveO59L]

for rule 1, how is cell C14 populated?

 

[Steve1208]

Via a data validation drop down list.

 

[Steve1208]

I have tried the following VBA code, however it doesn't seem to work.

If Target.Address = Range("C16").Value = "Select" Then
Range("Rows 18:34").Select
Selection.EntireRow.Hidden = True
End If

Please help.

 

[SteveO59L]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 14 And Target.Value = "Select" Then
Rows("20:34").EntireRow.Hidden = True
End If
If Target.Column = 3 And Target.Row = 14 And Target.Value <> "Select" Then
Rows("20:34").EntireRow.Hidden = False
End If
End Sub

 

[Steve1208]

Hey thank you for your input, however the code does not work on the sheet I require it on.

I tried it on a test sheet and it worked perfectly.

I wonder if its because cells C14 is actually merged. C14:J15

Also the cells I want to hide, some of them are merged too. (A20:B34).

Not sure how to amend the code.

Again any help would be greatly appreciated.

 

[Steve1208]

Actually no I think it is having trouble with the code, because the sell mentioned is populated via a data validation drop down list.

 

[Steve1208]

I tested this again today. I'm sure the code is not activating because the cell containing the key word is populated by drop down list.

Does anyone know how to alter the code to recognise the drop down list data?

Thanks.

 

[Steve1208]

After lots of trial and error! mainly error! I have now found why the code is not working the way I want it to. please see below:

This is the code I want to add, as "Steve059L" kindly provided me with.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 16 And Target.Value = "Select" Then
Rows("18:34").EntireRow.Hidden = True
End If
If Target.Column = 3 And Target.Row = 16 And Target.Value <> "Select" Then
Rows("18:34").EntireRow.Hidden = False
End If
End Sub

The code works perfectly on it own, however the problems occur, when you have another code that wants to modify cells within the hidden range.
For example I want this code to be modifying the cells, which are in the hidden range.

If Target.Address = Range("C16").Address Then
Range("F20:F34").Value = ""
End If


Again help would be greatly appreciated!
Please let me know if more information is required.

 

[SteveO59L]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 16 And Target.Value = "Select" Then

Range("F20:F34").Value = ""

Rows("18:34").EntireRow.Hidden = True
End If
If Target.Column = 3 And Target.Row = 16 And Target.Value <> "Select" Then
Rows("18:34").EntireRow.Hidden = False
End If
End Sub