Ufdjrw

I would like to have some help with automatizing my data sheet.

In the range of E7:V7 (hereinafter, r) I have same drop down lists, each of them has four different values ("-"; "open"; "close"; "both").

When r contains only "-", I would like to have rows 21:50 hidden.

• 
  "open" shows rows 21:30
  


• 
  "close" shows rows 31:50
  


• 
  "both" shows rows 21:50
  

For example:

• if E7= "-", F7="open", then rows 21:30 are shown and 31:50 hidden.


• if E7="-", F7="both", then all rows are shown.

I hope it was clear enough.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Range("E7").Value = "-" Then
 Rows("21:50").EntireRow.Hidden = True
 ElseIf Range("E7").Value = "open" Then
 Rows("21:30").EntireRow.Hidden = False
 Rows("31:50").EntireRow.Hidden = True
 ElseIf Range("E7").Value = "close" Then
 Rows("31:50").EntireRow.Hidden = False
 Rows("21:30").EntireRow.Hidden = True
 ElseIf Range("E7").Value = "both" Then
 Rows("21:50").EntireRow.Hidden = False
 End If
End Sub

This code works only for one criteria, but I hope it helps to clarify the situation.

As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim AffectedCells As Range
 Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))

 If Not AffectedCells Is Nothing Then
 Dim Cell As Range
 For Each Cell In AffectedCells
 Select Case Cell.Value
 Case "-"
 Target.Parent.Rows("21:50").Hidden = True
 Case "open"
 Target.Parent.Rows("21:30").Hidden = False
 Target.Parent.Rows("31:50").Hidden = True
 Case "close"
 Target.Parent.Rows("31:50").Hidden = False
 Target.Parent.Rows("21:30").Hidden = True
 Case "both"
 Target.Parent.Rows("21:50").Hidden = False
 End Select
 Next Cell
 End If
End Sub