VBA, hiding cells or hide rows!

**Raanan** · 09-04-2013, 05:56 AM

Morning, Me again. confusing myself again. either that or asking vba/excel to do too much

I have two drop down boxes on a worksheet.

C1 drop down contains - "Handler, Team, Both".. and the following code in VBa works behind it

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then
        If Range("C1").Value = "Handler" Then

            Rows("22:100").EntireRow.Hidden = True
            Rows("8:21").EntireRow.Hidden = False
    
    ElseIf Range("C1").Value = "Team" Then
            Rows("22:100").EntireRow.Hidden = False
            Rows("8:21").EntireRow.Hidden = True

    ElseIf Range("C1").Value = "Both" Then
            Rows("22:100").EntireRow.Hidden = False
            Rows("8:21").EntireRow.Hidden = False
End If
    End If
End Sub

And i Also have C3 with months of the year with the following working behind it

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        If Range("C3").Value = "Jan" Then
            'You can reference the row directly on the same sheet
            'and do not need ActiveSheet
            Columns("D:D").EntireColumn.Hidden = False
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
           
        
        ElseIf Range("C3").Value = "Feb" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = False
            Columns("F:F").EntireColumn.Hidden = True
            
            
        ElseIf Range("C3").Value = "Mar" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = False
            
            
        ElseIf Range("C3").Value = "Apr" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
           
            
        ElseIf Range("C3").Value = "May" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
            
        
        ElseIf Range("C3").Value = "Jun" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
            
        
        ElseIf Range("C3").Value = "Jul" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
         
        
     ElseIf Range("C3").Value = "Aug" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
          
        
     ElseIf Range("C3").Value = "Sep" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            
        
    ElseIf Range("C3").Value = "Oct" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
           
        
    ElseIf Range("C3").Value = "Nov" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
           
            
    ElseIf Range("C3").Value = "Dec" Then
            Columns("D:D").EntireColumn.Hidden = True
            Columns("E:E").EntireColumn.Hidden = True
            Columns("F:F").EntireColumn.Hidden = True
            
            
        ElseIf Range("C3").Value = "All" Then
            Columns("D:D").EntireColumn.Hidden = False
            Columns("E:E").EntireColumn.Hidden = False
            Columns("F:F").EntireColumn.Hidden = False
            
            
    End If
    End If
End Sub

'truncated to match text limit'

The problem is I cant get them both to work on the same vba.

If i put the code individually it works fine. Put them together and they don't work.

I Do want them to work together so if they select Handler, Jan. It hides Feb-Dec and Team data.

can i put them as seperate subs to work, or is there something im missing?

I'm hoping its a simple fix rather than a "if C1=Team and C3="Jan" hide E8:O21 or something similar

Thanks