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

You can't have 2 same name procedures within the same class. What you
need to do is merge the code from the 2nd one into the 1st one. I might
get time to rework an example later, but have a go at it anyway!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
'This is the same for both
If Target.Cells.Count 1 Then Exit Sub
If Target.Value = "" Then Range("7:90,91:186").Rows.Hidden = False

'Store values, row refs
Const sValuesB5$ = "Dragline|Shovel|Aux|DraglineNP"
Const sRowsToHideB5$ = "91:186|7:90,142:186|7:141,163:186|7:162"
Const sRowsToUnhideB5$ = "7:90|91:141|142:162|163:186"
Const sValuesE5$ = "Unknown|Olex|Pirelli|Amercable|Prysmian|MM
Cables"
Const sRowsToHideE5$ =
"11:186|7:10,21:186|7:30,41:186|7:40,51:186|7: 50"
Const sRowsToUnhideE5$ = "7:10|11:20|21:30|31:40|41:50|51:162"

Dim aValues, aRowsToHide, aRowsToUnhide, n&

'Dtermine which cell changed
Select Case Target.Address
Case "$B$5"
aValues = Split(sValuesB5, "|")
aRowsToHide = Split(sRowsToHideB5, "|")
aRowsToUnhide = Split(sRowsToUnhideB5, "|")
GoTo HideUnhideRows

Case "$E$5"
aValues = Split(sValuesE5, "|")
aRowsToHide = Split(sRowsToHideE5, "|")
aRowsToUnhide = Split(sRowsToUnhideE5, "|")
GoTo HideUnhideRows
End Select 'Case Target.Address

Normalexit:
Exit Sub

HideUnhideRows:
Application.EnableEvents = False
For n = LBound(aValues) To UBound(aValues)
If Target.Value = aValues(n) Then
Range(aRowsToUnhide(n)).Rows.Hidden = False
Range(aRowsToHide(n)).Rows.Hidden = True
End If
Next
Application.EnableEvents = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion