Compile Error: procedure too large

[DillonAlex]

I have a huge macro in the "ThisWorkbook"

The macro exceeds 64K and I am using SheetChange so I cannot seperate the macro into smaller macros because I need to use "ThisWorkbook" and not any modules

What do I do to get around this error. Can I create 2 seperate "ThisWorkbook"?

Someone please help!!!

 

[Jonmo1]

I don't uderstand why you can't seperate it out into smaller subroutines...AND store them in standard modules...

I have this in the "ThisWorkbook" module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
test2
End Sub

And this in a seperate Standard Module

Sub test2()
MsgBox "hello"
End Sub

Works fine.

 

[DillonAlex]

The macro is essential this but like over 400 of these

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'AUTO-IMMUNE'

'BLOOD DISORDER'

If Worksheets("Input").Rows("34:57").Hidden = False Then

Select Case Range("BloodDisorderQ1")
Case "Yes"
Sheets("Input").Select
Rows("36:37").Select
Selection.EntireRow.Hidden = False
Range("B36:D37").Select
Case "No"
Sheets("Input").Select
Rows("36:37").Select
Selection.EntireRow.Hidden = True
Range("B38:D39").Select
End Select

Select Case Range("BloodDisorderQ3")
Case "Yes"
Sheets("Input").Select
Rows("40:41").Select
Selection.EntireRow.Hidden = False
Range("B40:D41").Select
Case "No"
Sheets("Input").Select
Rows("40:41").Select
Selection.EntireRow.Hidden = True
Range("B42:D43").Select
End Select

End If

 

[DillonAlex]

How do I seperate that into a smaller subroutine

 

[dave3009]

For starts you don't need to use a Select Case statement for what is essential a boolean, you could use a more simple if like this

With Worksheets("Input")
    If .Rows("34:57").Hidden = False Then
        
        If Range("BloodDisorderQ1") = "Yes" Then
            .Rows("36:37").Hidden = False
        Else
            .Rows("36:37").Hidden = True
        End If
        
        If Range("BloodDisorderQ3") = "Yes" Then
            .Rows("40:41").Hidden = False
        Else
            .Rows("40:41").Hidden = True
        End If
    
    End If

End With

However there is a pattern in your code so you could probably loop through each BloodDisorder group using just the one (or maybe a couple) of simple If statements

 

[wigi]

First, have a look at reducing code lines and eliminating useless actions. For example, this:

Sheets("Input").Select
Rows("36:37").Select
Selection.EntireRow.Hidden = False
Range("B36:D37").Select

is equivalent to:

Sheets("Input").Rows("36:37").Hidden = False

This will solve a number of problems:

- a lot of code lines will vanish
- the code executes faster
- the screen will not flicker that much
- ...

 

[Jonmo1]

All those lines that Select and then use Selection are unneeded, and bloating your code to the point that the procedure is too large.

You can manipulate sheets and ranges without selecting them.

example, that code you just posted, could be done like this.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
'AUTO-IMMUNE'
'BLOOD DISORDER'
With Sheets("Input") '<- this creates a reference to that sheet,
                     'anywhere that sheet is specified I can now just preced it with a period.
    If .Rows("34:57").Hidden = False Then
        Select Case Range("BloodDisorderQ1")
            Case "Yes"
                .Rows("36:37").EntireRow.Hidden = False
            Case "No"
                .Rows("36:37").EntireRow.Hidden = True
        End Select
 
        Select Case Range("BloodDisorderQ3")
            Case "Yes"
                .Rows("40:41").EntireRow.Hidden = False
            Case "No"
                .Rows("40:41").EntireRow.Hidden = True
        End Select
    End If
End With
End Sub

 

[Jonmo1]

Also, considering that the Ranges you are testing (BloodDisorderQ1 and Q3) each have only 2 possible values, Yes or No...
That can be reduced further into a sort of Toggle...
If it's No, then Hidden = True, otherwise Hidden = False

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
'AUTO-IMMUNE'
'BLOOD DISORDER'
With Sheets("Input")
    If .Rows("34:57").Hidden = False Then
        .Rows("36:37").EntireRow.Hidden = Range("BloodDisorderQ1").Value = "No"
        .Rows("40:41").EntireRow.Hidden = Range("BloodDisorderQ3").Value = "No"
    End If
End With
End Sub

 

[DillonAlex]

How would I reduce this code

Select Case Range("CancerQ124")
Case "Yes"
Sheets("Input").Select
Rows("617:618").Select
Selection.EntireRow.Hidden = False
Rows("621:622").Select
Selection.EntireRow.Hidden = False
Rows("609:616").Select
Selection.EntireRow.Hidden = True
Range("B617:D618").Select
Case "No"
Sheets("Input").Select
Rows("609:610").Select
Selection.EntireRow.Hidden = False
Rows("617:624").Select
Selection.EntireRow.Hidden = True
Range("B609:D610").Select
End Select

 

[wigi]

How would I reduce this code

Select Case Range("CancerQ124")
Case "Yes"
Sheets("Input").Select
Rows("617:618").Select
Selection.EntireRow.Hidden = False
Rows("621:622").Select
Selection.EntireRow.Hidden = False
Rows("609:616").Select
Selection.EntireRow.Hidden = True
Range("B617:D618").Select
Case "No"
Sheets("Input").Select
Rows("609:610").Select
Selection.EntireRow.Hidden = False
Rows("617:624").Select
Selection.EntireRow.Hidden = True
Range("B609:D610").Select
End Select

Please study and work on the codes that were already written for you.
They contain the necessary and sufficient information to answer this question.
Please use

 tags. It's the # icon when you are composing a message.