VBA Procedure too large; having trouble running a sub procedure

[Tcestnick]

I've divided an excel worksheet into different sections, each section being 20 rows in size (example: Section 1 = rows 132 to 152, Section 2 = rows 153 to 173, etc.). There are 100 sections. I'm running a procedure in VBA that, for each section, will hide a certain number of rows based on a desired number of rows for each section. After about running the routine on 23 sections, I get a "Procedure too Large" error. I was told that I should run a sub procedure to solve the problem. I'm not sure how to do that. I'm using Sub proc1(), but it's not working. Here is the first part of the code, followed by the Sub proc1(). It's not working. I'm now getting the following error immediately after the Sup proc1() line:

Ambiguous name detected: Worksheet_Change

If anyone can help me with the proper code after the Sub proc1() that would help immensely!

```

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
ActiveSheet.Activate
If Not Application.Intersect(Range("G20"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "0":  Rows("132:152").EntireRow.Hidden = True
Case Is = "1":  Rows("134:152").EntireRow.Hidden = True
                Rows("123:133").EntireRow.Hidden = False
Case Is = "2":  Rows("135:152").EntireRow.Hidden = True
                Rows("123:134").EntireRow.Hidden = False
Case Is = "3":  Rows("136:152").EntireRow.Hidden = True
                Rows("123:135").EntireRow.Hidden = False
Case Is = "4":  Rows("137:152").EntireRow.Hidden = True
                Rows("123:136").EntireRow.Hidden = False
Case Is = "5":  Rows("138:152").EntireRow.Hidden = True
                Rows("123:137").EntireRow.Hidden = False
Case Is = "6":  Rows("139:152").EntireRow.Hidden = True
                Rows("123:138").EntireRow.Hidden = False
Case Is = "7":  Rows("140:152").EntireRow.Hidden = True
                Rows("123:139").EntireRow.Hidden = False
Case Is = "8":  Rows("141:152").EntireRow.Hidden = True
                Rows("123:140").EntireRow.Hidden = False
Case Is = "9":  Rows("142:152").EntireRow.Hidden = True
                Rows("123:141").EntireRow.Hidden = False
Case Is = "10":  Rows("143:152").EntireRow.Hidden = True
                Rows("123:142").EntireRow.Hidden = False
Case Is = "11":  Rows("144:152").EntireRow.Hidden = True
                Rows("123:143").EntireRow.Hidden = False
Case Is = "12":  Rows("145:152").EntireRow.Hidden = True
                Rows("123:144").EntireRow.Hidden = False
Case Is = "13":  Rows("146:152").EntireRow.Hidden = True
                Rows("123:145").EntireRow.Hidden = False
Case Is = "14":  Rows("147:152").EntireRow.Hidden = True
                Rows("123:146").EntireRow.Hidden = False
Case Is = "15":  Rows("148:152").EntireRow.Hidden = True
                Rows("123:147").EntireRow.Hidden = False
Case Is = "16":  Rows("149:152").EntireRow.Hidden = True
                Rows("123:148").EntireRow.Hidden = False
Case Is = "17":  Rows("150:152").EntireRow.Hidden = True
                Rows("123:149").EntireRow.Hidden = False
Case Is = "18":  Rows("151:152").EntireRow.Hidden = True
                Rows("123:150").EntireRow.Hidden = False
Case Is = "19":  Rows("152:152").EntireRow.Hidden = True
                Rows("123:151").EntireRow.Hidden = False
Case Is = "20":  Rows("123:152").EntireRow.Hidden = False

End Select
End If

```
and so on...then:
```

If Not Application.Intersect(Range("G43"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
                   
Case Is = "0": Rows("615:635").EntireRow.Hidden = True
Case Is = "1": Rows("617:635").EntireRow.Hidden = True
    Rows("615:616").EntireRow.Hidden = False
Case Is = "2": Rows("618:635").EntireRow.Hidden = True
    Rows("615:617").EntireRow.Hidden = False
Case Is = "3": Rows("619:635").EntireRow.Hidden = True
    Rows("615:618").EntireRow.Hidden = False
Case Is = "4": Rows("620:635").EntireRow.Hidden = True
    Rows("615:619").EntireRow.Hidden = False
Case Is = "5": Rows("621:635").EntireRow.Hidden = True
    Rows("615:620").EntireRow.Hidden = False
Case Is = "6": Rows("622:635").EntireRow.Hidden = True
    Rows("615:621").EntireRow.Hidden = False
Case Is = "7": Rows("623:635").EntireRow.Hidden = True
    Rows("615:622").EntireRow.Hidden = False
Case Is = "8": Rows("624:635").EntireRow.Hidden = True
    Rows("615:623").EntireRow.Hidden = False
Case Is = "9": Rows("625:635").EntireRow.Hidden = True
    Rows("615:624").EntireRow.Hidden = False
Case Is = "10": Rows("626:635").EntireRow.Hidden = True
    Rows("615:625").EntireRow.Hidden = False
Case Is = "11": Rows("627:635").EntireRow.Hidden = True
    Rows("615:626").EntireRow.Hidden = False
Case Is = "12": Rows("628:635").EntireRow.Hidden = True
    Rows("615:627").EntireRow.Hidden = False
Case Is = "13": Rows("629:635").EntireRow.Hidden = True
    Rows("615:628").EntireRow.Hidden = False
Case Is = "14": Rows("630:635").EntireRow.Hidden = True
    Rows("615:629").EntireRow.Hidden = False
Case Is = "15": Rows("631:635").EntireRow.Hidden = True
    Rows("615:630").EntireRow.Hidden = False
Case Is = "16": Rows("632:635").EntireRow.Hidden = True
    Rows("615:631").EntireRow.Hidden = False
Case Is = "17": Rows("633:635").EntireRow.Hidden = True
    Rows("615:632").EntireRow.Hidden = False
Case Is = "18": Rows("634:635").EntireRow.Hidden = True
    Rows("615:633").EntireRow.Hidden = False
Case Is = "19": Rows("635:635").EntireRow.Hidden = True
    Rows("615:634").EntireRow.Hidden = False
Case Is = "20": Rows("615:635").EntireRow.Hidden = False
                   
End Select
End If

Call proc1
Call proc2

End Sub

Sub proc1()


If Not Application.Intersect(Range("G44"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
                   
Case Is = "0": Rows("636:656").EntireRow.Hidden = True
Case Is = "1": Rows("638:656").EntireRow.Hidden = True
    Rows("636:637").EntireRow.Hidden = False
Case Is = "2": Rows("639:656").EntireRow.Hidden = True
    Rows("636:638").EntireRow.Hidden = False
Case Is = "3": Rows("640:656").EntireRow.Hidden = True
    Rows("636:639").EntireRow.Hidden = False
Case Is = "4": Rows("641:656").EntireRow.Hidden = True
    Rows("636:640").EntireRow.Hidden = False
Case Is = "5": Rows("642:656").EntireRow.Hidden = True
    Rows("636:641").EntireRow.Hidden = False
Case Is = "6": Rows("643:656").EntireRow.Hidden = True
    Rows("636:642").EntireRow.Hidden = False
Case Is = "7": Rows("644:656").EntireRow.Hidden = True
    Rows("636:643").EntireRow.Hidden = False
Case Is = "8": Rows("645:656").EntireRow.Hidden = True
    Rows("636:644").EntireRow.Hidden = False
Case Is = "9": Rows("646:656").EntireRow.Hidden = True
    Rows("636:645").EntireRow.Hidden = False
Case Is = "10": Rows("647:656").EntireRow.Hidden = True
    Rows("636:646").EntireRow.Hidden = False
Case Is = "11": Rows("648:656").EntireRow.Hidden = True
    Rows("636:647").EntireRow.Hidden = False
Case Is = "12": Rows("649:656").EntireRow.Hidden = True
    Rows("636:648").EntireRow.Hidden = False

....and so on

 

[maabadi]

Please check this (Untested):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr As Long
    Dim i As Long
    Dim k As Long
 Application.EnableEvents = True
 Application.ScreenUpdating = False
 ActiveSheet.Unprotect
 ActiveSheet.Activate
'   Set initial value of next row
    nr = 21

For i = 1 To 100 Step 1
     If Not Application.Intersect(Range("G" & nr + i - 2), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
    Case Is = "0": Rows("nr * (i + 5 ) + 6 : nr * (i + 6 ) + 5").EntireRow.Hidden = True
For k = 1 To 19
    Case Is = "k": Rows("nr * (i + 5 ) + k + 7 : nr * (i + 6 ) + 5").EntireRow.Hidden = True
                   Rows("nr * (i + 5 ) + 6 : nr * (i + 5 ) + 6 + k").EntireRow.Hidden = False
 Next k

     Case Is = "20": Rows("nr * (i + 5 ) + 6 : nr * (i + 6 ) + 5").EntireRow.Hidden = False
End Select
End If
Next i

Application.EnableEvents = False
Application.ScreenUpdating = True
End Sub

 

[maabadi]

I have problems always with Intersect then change code to this and worked for macro but not in worksheet change event.
I think you don't have problems with Intersect and can correct yourself.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr As Long
    Dim i As Long
    Dim k As Long
  Application.EnableEvents = True
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.Activate
'   Set initial value of next row
    nr = 21
'   Copy range
For i = 1 To 100 Step 1
    Set Target = Range("G" & nr + i - 2)
     If Target.Value <> "" Then
Select Case Target.Value
    Case Is = 0
                    Rows(nr * (i + 5) + 6 & ":" & nr * (i + 6) + 5).EntireRow.Hidden = True
    GoTo NextCase
End Select
For k = 1 To 19
Select Case Target.Value
    Case Is = k
                   Rows(nr * (i + 5) + k + 7 & ":" & nr * (i + 6) + 5).EntireRow.Hidden = True
                   Rows(nr * (i + 5) + 6 & ":" & nr * (i + 5) + 6 + k).EntireRow.Hidden = False
    GoTo NextCase
End Select
Next k
Select Case Target.Value
     Case Is = 20
                   Rows(nr * (i + 5) + 6 & ":" & nr * (i + 6) + 5).EntireRow.Hidden = False
     GoTo NextCase
End Select
End If
NextCase:
Next i

Application.EnableEvents = False
Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about doing it like

Private Sub Worksheet_Change(ByVal Target As Range)
   ActiveSheet.Unprotect
   If Target.Address(0, 0) = "G20" Then
      Rows("123:152").Hidden = False
      Select Case Target.Value
         Case 1 To 19: Rows(133 + Target.Value & ":152").Hidden = True
      End Select
   If Target.Address(0, 0) = "G43" Then
      Rows("615:635").Hidden = False
      Select Case Target.Value
         Case 1 To 19: Rows(616 + Target.Value & ":152").Hidden = True
      End Select
   End If
   ActiveSheet.Protect
End Sub

 

[maabadi]

Fluff your Code is good , But I think he want Source more than 100 Cell: from G20 to G120 OR ....

 

[Fluff]

He may well do, but I have just shown how to simplify the code.
Without knowing the exact rows involved, I can't do much more.

 

[Tcestnick]

Thank you to both of you! I'm sorry that I'm a newbie at this programming. Fluff, I'm wondering if you can help me slightly revise your code so that it works. In my case, my source cells are from G20 to G119 inclusive, corresponding to 100 "sections" in my worksheet. Section #1 is Rows 132 to 152. So, if I enter, say, the number 5 in cell G20, all but 5 rows are hidden in the section 1 (i.e. rows 132 to 137 are visible, and rows 138 to 152 are hidden; row 132 is a header row and the five rows below that are for data). IF I enter, say, the number 10 in cell G21, then this corresponds to section 2 (rows 153 to 173) and it will hide all but 10 rows in that section (i.e. rows 153 to 163 are visible, and rows 164 to 173 are hidden). And so on. My 100 sections are as follows: Section 1 (rows 132 to 152), Section 2 (rows 153 to 73)....Section 100 (rows 2211 to 2231). Any help tweaking your code would really help! Thanks!

 

[Fluff]

Does the code I posted work for G20 & G43?

 

[Fluff]

Scrub that, how about

Private Sub Worksheet_Change(ByVal Target As Range)
'   ActiveSheet.Unprotect
   If Not Intersect(Target, Range("G20:G119")) Is Nothing Then
      Rows((Target.Row - 20) * 21 + 132).Resize(21).Hidden = True
      Select Case Target.Value
         Case 1 To 20
            Rows((Target.Row - 20) * 21 + 132).Resize(Target + 1).Hidden = False
      End Select
   End If
'   ActiveSheet.Protect
End Sub

 

[Tcestnick]

Thanks Fluff. Your most recent coding works when I unprotect the sheet on my own. If I don't, it has this error:

Also, if I try to copy a figure in column G and copy down to several cells in G column below it, it get an error that says: Runtime error mismatch, and debugging shows the code "Case 1 to 20" as highlighted in yellow.