CODEWITHSUNDEEP
excelvba
James
James

Reputation: 1

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

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?

Upvotes: 0

Views: 122

Answers (1)

James
James

Reputation: 1

@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.

Upvotes: 0

Related Questions