Multiple Select Case Statements in a Single Procedure

Question

Using Select Case statements and Autofilters in a single function, I'm attempting to Autofilter two tables. I'm not sure when it is ideal to utilise modules vs. worksheets, therefore I'm wondering whether this is my problem that these are running from the worksheet itself rather as a separate module.

RegionChoice and ProjectType are two discrete dropdown fields that are the targets of two independent Select Case statements. The RegionChoice-based autofilters (the first of these select case statements) function flawlessly. The filters in the second Select Case statements, however, don't seem to function at all.

Both tables should be affected by a filter based on RegionChoice, but only one table should be affected by a filter based on ProjectType. Thanks and please refer to the code below.

'Autofilter table on Summary Tab & CapEx Project Table on Visual based on Region drop down on Visual Tab
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSumm As Worksheet, wsVis As Worksheet
    Dim range_to_filter As Range, range_to_filter_2 As Range
    
    Set wsSumm = ThisWorkbook.Worksheets("Summary")
    Set wsVis = ThisWorkbook.Worksheets("Visual")
    Set range_to_filter = wsSumm.Range("A3:Z113")
    Set range_to_filter_2 = wsVis.Range("B80:T109")
    
    If Application.Intersect(Me.Range("RegionChoice"), Target) Is Nothing Then Exit Sub

    Select Case Me.Range("RegionChoice").Value
    
    'Central
        Case Me.Range("A1").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="C"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="C"
    'South
        Case Me.Range("A2").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="S"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="S"
    'West
        Case Me.Range("A3").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="W"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="W"
    'Northeast
        Case Me.Range("A4").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="NE"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="NE"
    'Clear
        Case Me.Range("A5").Value
            wsSumm.Unprotect ("fac1")
           range_to_filter.AutoFilter Field:=4
           range_to_filter_2.AutoFilter Field:=5

    End Select
    
    If Application.Intersect(Me.Range("ProjectType"), Target) Is Nothing Then Exit Sub

    Select Case Me.Range("ProjectType").Value
    
    'Refresh
        Case Me.Range("A9").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Refresh"
    'Buildout
        Case Me.Range("A10").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Buildout"
    'Maintenance
        Case Me.Range("A11").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Maintenance"
    'Expansion
      Case Me.Range("A12").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Expansion"
    'Furniture
      Case Me.Range("A13").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Furniture"
    'Clear
      Case Me.Range("A14").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2

    End Select
End Sub

This was originally written as a series of if/else statements, but after receiving input from a SO user, I changed it to Select Case. I'd prefer to preserve it if it's possible because it has proven to be a far more effective approach.

I anticipated that the second Select Case statement using cell ProjectType would function similarly to the first using RegionChoice. In any case, the autofilter statements are not running.

Answer 1

Here's a slightly different structuring that will reduce your duplicated code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSumm As Worksheet, wsVis As Worksheet
    Dim range_to_filter As Range, range_to_filter_2 As Range, crit As String
    
    Set wsSumm = ThisWorkbook.Worksheets("Summary")
    Set wsVis = ThisWorkbook.Worksheets("Visual")
    Set range_to_filter = wsSumm.Range("A3:Z113")
    Set range_to_filter_2 = wsVis.Range("B80:T109")
    
    If Not Application.Intersect(Me.Range("RegionChoice"), Target) Is Nothing Then

        Select Case Me.Range("RegionChoice").Value
            Case Me.Range("A1").Value: crit = "C"
            Case Me.Range("A2").Value: crit = "S"
            Case Me.Range("A3").Value: crit = "W"
            Case Me.Range("A4").Value: crit = "NE"
            Case Me.Range("A5").Value: crit = ""
            Case Else: crit = ""
        End Select
        
        ApplyFilter range_to_filter, 4, crit
        ApplyFilter range_to_filter2, 5, crit
    End If
        
    If Not Application.Intersect(Me.Range("ProjectType"), Target) Is Nothing Then

        Select Case Me.Range("ProjectType").Value
            Case Me.Range("A9").Value: crit = "Refresh"
            Case Me.Range("A10").Value: crit = "Buildout"
            Case Me.Range("A11").Value: crit = "Maintenance"
            Case Me.Range("A12").Value: crit = "Expansion"
            Case Me.Range("A13").Value: crit = "Furniture"
            Case Me.Range("A14").Value: crit = ""
            Case Else: crit = ""
        End Select
        
        ApplyFilter range_to_filter_2, 2, crit
    End If
            
End Sub

'Apply a filter to a range `rng`, using field number `fldNum`
Sub ApplyFilter(rng As Range, fldNum As Long, crit As String)
    If Len(crit) > 0 Then
        rng.AutoFilter Field:=fldNum, Criteria1:=crit
    Else
        rng.AutoFilter Field:=fldNum
    End If
End Sub