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

4653

New Member
Joined
Apr 20, 2012
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.
VBA 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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Unless there is something I've missed, your entire sample can be compressed to this
VBA Code:
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,865
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Apr 20, 2012
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Unless there is something I've missed, your entire sample can be compressed to this
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
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 :oops: Somehow I doubt that we're alone in that respect.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top