Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to have 2 drop down lists filter rows
i've writen the code so that when i select an option from cell 'B5' it hides all rows except for those with 'dragline'. Same goes for selecting 'shovel' 'aux' or 'draglineNP'.
But i also have a drop down list in cell 'E5' that i want to work in the same way as above but with a different category, but after coping the same code underneath and changing the categories i get compile error: ambiguous name detected: worksheet_Change so i don't know how to word it to run multples of this kind of function in the one worksheet and then i have a couple more drop down list so i'd like to know how i go about it Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address < "$B$5" Then Exit Sub Application.EnableEvents = False If Target.Value = "" Then Range("7:90").EntireRow.Hidden = False Range("91:186").EntireRow.Hidden = False End If If Target.Value = "Dragline" Then Range("7:90").EntireRow.Hidden = False Range("91:186").EntireRow.Hidden = True End If If Target.Value = "Shovel" Then Range("91:141").EntireRow.Hidden = False Range("7:90,142:186").EntireRow.Hidden = True End If If Target.Value = "Aux" Then Range("142:162").EntireRow.Hidden = False Range("7:141,163:186").EntireRow.Hidden = True End If If Target.Value = "DraglineNP" Then Range("163:186").EntireRow.Hidden = False Range("7:162").EntireRow.Hidden = True End If Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address < "$E$5" Then Exit Sub Application.EnableEvents = False If Target.Value = "" Then Range("7:90").EntireRow.Hidden = False Range("91:186").EntireRow.Hidden = False End If If Target.Value = "Unknown" Then Range("7:10").EntireRow.Hidden = False Range("11:186").EntireRow.Hidden = True End If If Target.Value = "Olex" Then Range("11:20").EntireRow.Hidden = False Range("7:10,21:186").EntireRow.Hidden = True End If If Target.Value = "Pirelli" Then Range("21:30").EntireRow.Hidden = False Range("7:20,31:186").EntireRow.Hidden = True End If If Target.Value = "Amercable" Then Range("31:40").EntireRow.Hidden = False Range("7:30,41:186").EntireRow.Hidden = True End If If Target.Value = "Prysmian" Then Range("41:50").EntireRow.Hidden = False Range("7:40,51:186").EntireRow.Hidden = True End If If Target.Value = "MM Cables" Then Range("51:162").EntireRow.Hidden = False Range("7:50").EntireRow.Hidden = True End If Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data in rows not showing in auto-filter drop down | Excel Worksheet Functions | |||
Filter drop down lists | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Filter arrows on drop down lists | Excel Discussion (Misc queries) | |||
How to make drop-down lists show more rows | Excel Programming |