Procedure Too Large in private sub worksheet_selectionchange(byval target as range)-CodePudding

I'm new to VBA and thank you in advance. :)

Macro: In short, this macro will execute another macro when the wanted cell is active.

Problem: This macro works fine till column X adding more after that produces the "procedure too large" error. I think it has reached its capacity limit. How do I make the code shorter/work?

Note: this code continues till AA-column and the only thing that changes are the range columns ("B11"->"C11") and code (B_11 -> C_11)

Code:

`Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then

' B-Column Click Macro------------------------------------------------------------

If Not Intersect(Target, Range("B11")) Is Nothing Then Call B_11
If Not Intersect(Target, Range("B12")) Is Nothing Then Call B_12
If Not Intersect(Target, Range("B13")) Is Nothing Then Call B_13
If Not Intersect(Target, Range("B14")) Is Nothing Then Call B_14

If Not Intersect(Target, Range("B16")) Is Nothing Then Call B_16
If Not Intersect(Target, Range("B17")) Is Nothing Then Call B_17

If Not Intersect(Target, Range("B19")) Is Nothing Then Call B_19
If Not Intersect(Target, Range("B20")) Is Nothing Then Call B_20
If Not Intersect(Target, Range("B21")) Is Nothing Then Call B_21
If Not Intersect(Target, Range("B22")) Is Nothing Then Call B_22

If Not Intersect(Target, Range("B24")) Is Nothing Then Call B_24
If Not Intersect(Target, Range("B25")) Is Nothing Then Call B_25
If Not Intersect(Target, Range("B26")) Is Nothing Then Call B_26
If Not Intersect(Target, Range("B27")) Is Nothing Then Call B_27

' C-Column Click Macro------------------------------------------------------------

If Not Intersect(Target, Range("C11")) Is Nothing Then Call C_11
If Not Intersect(Target, Range("C12")) Is Nothing Then Call C_12
If Not Intersect(Target, Range("C13")) Is Nothing Then Call C_13
If Not Intersect(Target, Range("C14")) Is Nothing Then Call C_14

If Not Intersect(Target, Range("C16")) Is Nothing Then Call C_16
If Not Intersect(Target, Range("C17")) Is Nothing Then Call C_17

If Not Intersect(Target, Range("C19")) Is Nothing Then Call C_19
If Not Intersect(Target, Range("C20")) Is Nothing Then Call C_20
If Not Intersect(Target, Range("C21")) Is Nothing Then Call C_21
If Not Intersect(Target, Range("C22")) Is Nothing Then Call C_22

If Not Intersect(Target, Range("C24")) Is Nothing Then Call C_24
If Not Intersect(Target, Range("C25")) Is Nothing Then Call C_25
If Not Intersect(Target, Range("C26")) Is Nothing Then Call C_26
If Not Intersect(Target, Range("C27")) Is Nothing Then Call C_27

` continues till range("AA11")... call AA_11

CodePudding user response：

Please, try the next way. I only supposed that your rest of the sausage code keeps the same pattern...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    If Target.count > 1 Then Exit Sub
    
    Set rng = Me.Range("B11:AA14,B16:AA18,B19:AA22,B24:AA27")
    If Not Intersect(Target, rng) Then
        MySub Target
    End If
End Sub

Sub MySub(Target As Range)
     MsgBox Target.Value & " _ " & Target.Address
End Sub

It calls MySub with the selected cell range as argument. You can do whatever you need with it. My example code only sends a message with the selected cell value and its address...

If you need to do different things with the selected cell, according to its position/address, please show two, three such examples code to join their logic in the unique Sub my above code uses.

CodePudding user response：

@Faneduru thank you for taking your time and answering my question. The code that is implemented in those cells are filters that sort data.

Here are examples of those codes:

Sub B_11()
'
' Area-082M


    Sheets("Tasks").Select
    
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    Columns("F:BI").Hidden = False
    Columns("J:BI").Hidden = True

    ActiveSheet.ListObjects("Table2435").Range.AutoFilter Field:=4, Criteria1:= _
        "082M"
End Sub
Sub B_12()
'
' Area-082M


    Sheets("Tasks").Select
    
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    Columns("F:BI").Hidden = False
    Columns("F:I").Hidden = True
    Columns("N:BI").Hidden = True
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter Field:=4, Criteria1:= _
        "082M"
Sub N_22()
'
' Area-122M

    Sheets("Tasks").Select
    
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter
    Columns("F:BI").Hidden = False
    Columns("F:AO").Hidden = True
    Columns("AT:BI").Hidden = True
    ActiveSheet.ListObjects("Table2435").Range.AutoFilter Field:=4, Criteria1:= _
        "122M"
End Sub