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

4653

New Member
Joined
Apr 20, 2012
Messages
27
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:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top