Hiding various rows based on 10+ options on drop down menu

Question

I have a file that pulls data into a "summary" page. I want to be able to hide certain rows based off cell "C4"s input. Cell C4 has a drop down menu that chooses multiple different product info. For example the first input on the C4 Dropdown has info from row 8-32. The others sometimes only have info from rows 8-12, 8-17, 8-24, etc.

Currently my code is this... Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("C4"), Target) Is Nothing And Target.Cells.Count = 1 Then
    Rows("12:31").Hidden = False
    
    Select Case Target.Value
        Case "Transaction Mail", "PPC Material Handling"
        Rows("12:31").Hidden = False
        Case "Parcels"
            Rows("27:31").Hidden = True
        Case "VEO", "Packets", "PIF Packets", "Admail"
            Rows("17:31").Hidden = True
        Case "PIF Material Handling", "IRU", "RVU", "PPC Others"
            Rows("12:31").Hidden = True
                      
        Case Else
      
       
       
    End Select
End If

End Sub

For some reason, this code will unhide everything for "Transaction mail" and hide the proper rows for "VEO" which is perfect, but every other variable that is inputted into the C4 cell, doesn't take.

Am I only limited to 2 variables? Should I be making multiple macros and have 1 macro basically activate them based off cell C4?

Next doesn't close an If.... End If does, for starters. — BigBen
Remove the On Error Resume Nexts as those just hide potential errors. — BigBen
Make sure the drop down does not have spaces or other unprintable characters that are not in the typed values in your select case. — Scott Craner
And you should have appended not replaced your old question. — Scott Craner
Yeah sorry mate about deleting the old stuff. yeah I have triple checked the Dropdown menu for spaces, even we typed it, its weird cause index and aggerate function is working of the same input cell and works perfectly its just hiding the cells with the macro thats continuing to be the issue. I don't know why "Transaction mail" and "VEO" would work when the rest don't. highly frustrating — James

James James · Accepted Answer · 2021-03-23T18:34:11

@scott Craner is the person who solved this. the code that works is...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("$C$4"), Target) Is Nothing And Target.Cells.Count = 1 Then
    Rows("12:31").Hidden = False
    
    Select Case Target.Value
        Case "Transaction Mail", "PPC Material Handling"
        Rows("12:31").Hidden = False

        
        Case "Parcels"
            Rows("27:31").Hidden = True
  
        
        Case "VEO", "Packets", "PIF Packets", "Admail"
            Rows("17:31").Hidden = True

        
        Case "PIF Material Handling", "IRU", "RVU", "PPC Others"
            Rows("12:31").Hidden = True
        Case Else
End Select
End If
End Sub`

The reason why it wasn't working for me before was due to spacing within the macro worksheet. for some reasons it wasn't capturing it, there was also a typo here and there. lesson learned. if there is an issue with code that looks right its probably syntax/typos. Again thanks @scott Craner. Legend.

Hiding various rows based on 10+ options on drop down menu

1 Answers