VBA Procedure too large; having trouble running a sub procedure

T

Tcestnick

New Member
Joined
Dec 1, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!

```
VBA Code: 
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:
```
VBA Code: 
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

VBA Code: 
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
 
Last edited by a moderator:
Sort by date Sort by votes
Ok, how about
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   ActiveSheet.Unprotect
   If Not Intersect(Target, Range("G20:G119")) Is Nothing Then
      For Each Cl In Intersect(Target, Range("G20:G119"))
         Rows((Cl.Row - 20) * 21 + 132).Resize(21).Hidden = True
         Select Case Cl.Value
            Case 1 To 20
               Rows((Cl.Row - 20) * 21 + 132).Resize(Cl + 1).Hidden = False
         End Select
      Next Cl
   End If
   ActiveSheet.Protect
End Sub
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

R
  • Question Question
Automate hide-unhide button based on heading style
Replies
0
Views
634
rahildhody
R
T
  • Question Question
Toggling a Checkbox VBA
Replies
2
Views
1K
termeric
T
J
  • Solved
how to random all numbers
Replies
7
Views
280
Fluff
Fluff
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
N
  • Question Question
Run-time Error "13" when I delete contents of a cell
Replies
7
Views
557
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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