Excel VBA loop on Private Sub Worksheet_Change(ByVal Target As Range)

[4653]

I know very little about VBA but I created a simple If/ElseIf code that is apparently too large because when I make the necessary change on the spreadsheet that runs the code it gives me a "Compile error: Procedure too large" message. Essentially, the user has a drop down list in each cell O31 through O39 that they can choose either Yes or No. If every single drop down on O31 through O39 = "No" then rows 107:108 are not visible but if even one of those drop downs = "Yes" then rows 107:108 is visible. How can I loop the below code so that I can avoid the "Compile error: Procedure too large" message? The problem is that I have to do the below code on 10 different columns and each column has different options from the drop down list that could make their respective rows visible or not visible but the structure of each code is virtually identical. Then I also have other If/ElseIf codes added for other items which is why I'm exceeding the allowable limit. I'm using Office 365 if that matters.

Below is a sample of the code.

If Target.Column = 15 And Target.Row = 31 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O31").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O31").Select
End If
End If
If Target.Column = 15 And Target.Row = 32 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O32").Select
       ElseIf Target.Value = "No" And Range("O31").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O32").Select
End If
End If
If Target.Column = 15 And Target.Row = 33 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O33").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O31").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O33").Select
End If
End If
If Target.Column = 15 And Target.Row = 34 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O34").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O31").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O34").Select
End If
End If
If Target.Column = 15 And Target.Row = 35 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O35").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O31").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O35").Select
End If
End If
If Target.Column = 15 And Target.Row = 36 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O36").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O31").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O36").Select
End If
End If
If Target.Column = 15 And Target.Row = 37 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O37").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O31").Value = "No" And Range("O38").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O37").Select
End If
End If
If Target.Column = 15 And Target.Row = 38 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O38").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O31").Value = "No" And Range("O39").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O38").Select
End If
End If
If Target.Column = 15 And Target.Row = 39 Then
        If Target.Value <> "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = False
            ActiveSheet.Range("O39").Select
       ElseIf Target.Value = "No" And Range("O32").Value = "No" And Range("O33").Value = "No" And Range("O34").Value = "No" And Range("O35").Value = "No" And Range("O36").Value = "No" And Range("O37").Value = "No" And Range("O38").Value = "No" And Range("O31").Value = "No" Then
            Application.Rows("107:108").Select
            Application.Selection.EntireRow.Hidden = True
            ActiveSheet.Range("O39").Select
End If
End If

 

[jasonb75]

Unless there is something I've missed, your entire sample can be compressed to this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O31:O39")) Is Nothing Then
    Range("107:108").EntireRow.Hidden = (Evaluate("COUNTIF(O31:O39,""No"")") = 9)
End If
End Sub

I expect that you could apply similar logic to the rest.

 

[mole999]

you could cut your code in half, and create a second sub, and call that when the first sub finishes

it is purley because its too long as a single sub on its own

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("O31:O39")) Is Nothing Then
      Rows("107:108").Hidden = [countif(O31:O39, "yes")] = 0
   End If
End Sub

 

[4653]

Unless there is something I've missed, your entire sample can be compressed to this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O31:O39")) Is Nothing Then
    Range("107:108").EntireRow.Hidden = (Evaluate("COUNTIF(O31:O39,""No"")") = 9)
End If
End Sub

I expect that you could apply similar logic to the rest.

I'm almost ashamed to say how long it took me to come up with that code, much less type it all up. Your way would have saved me a ton of time and actually worked. I can't thank you enough.

 

[jasonb75]

I'm almost ashamed to say how long it took me to come up with that code, much less type it all up.

I have similar thoughts when I look at some of my early attempts at writing code Somehow I doubt that we're alone in that respect.