Shorten VBA code

Hi,

I have two sheets... on first one there is drop-down menu with YES/NO and on the second I have hidden rows. If user select YES on first, row (with specific number.) un-hide on second sheet. NO or "" (empty) hide row. What I made works, but already when putting together I said that I'll ask you, for some other solution due to personal learning. But, always that but, now that I want to add solution to other column on a fist sheet I got error message that code is to long (Procedure too Large Error). So... part of mine code...

Private Sub Worksheet_Change(ByVal Target As Range)
...
    If Not Intersect(Target, Me.Range("$D$39:$E$39")) Is Nothing Then
       If Range("$D$39").Value = "NO" Or Range("$D$39").Value = "" Then
            Sheets("xxxx").Rows("57:57").EntireRow.Hidden = True
        ElseIf Range("$D$39").Value = "YES" Then
            Sheets("xxxx").Rows("57:57").EntireRow.Hidden = False
        End If
    End If
    If Not Intersect(Target, Me.Range("$D$40:$E$40")) Is Nothing Then
       If Range("$D$40").Value = "NO" Or Range("$D$40").Value = "" Then
            Sheets("xxxx").Rows("58:58").EntireRow.Hidden = True
        ElseIf Range("$D$40").Value = "YES" Then
            Sheets("xxxx").Rows("58:58").EntireRow.Hidden = False
        End If
    End If
    If Not Intersect(Target, Me.Range("$D$41:$E$41")) Is Nothing Then
       If Range("$D$41").Value = "NO" Or Range("$D$41").Value = "" Then
            Sheets("xxxx").Rows("59:59").EntireRow.Hidden = True
        ElseIf Range("$D$41").Value = "YES" Then
            Sheets("xxxx").Rows("59:59").EntireRow.Hidden = False
        End If
    End If
...
End Sub

As you can see 7 lines are repeating from row 39 to 118. At the moment just in D column, but needed also in E column. I believe that there can be done some "loop" or "for" or I don't know what... but... any ideas?

Thx and wish you all the best in 2018!


Cheers, Marko

It looks to be just 18 rows below your target.row, I also assume that sheets("xxx") is not the sheet with the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Me.Range("$D$39:$E$41")) Is Nothing Then
    
        If Target.Value = "NO" Or Target.Value = "" Then
        
            Sheets("xxxx").Range("A" & Target.Row + 18).EntireRow.Hidden = True
            
        ElseIf Target.Value = "YES" Then
        
            Sheets("xxxx").Range("A" & Target.Row + 18).EntireRow.Hidden = False
            
        End If
        
    End If

End Sub

... sometimes I feel so stupid! :-(

Thx for this... works perfect. And yes, simple and the best solution.

Cheers, Marko