Excel VBA Hide Rows based on Multiple Criteria-CodePudding

I'm still learning VBA but have started putting things together from what I have found on Google and Stackoverflow so far. But I can't seem to workout a fix for my issue, so Id appreciate your help please.

I am creating a document that has a list of different items, which I would like to appear or be hidden, depending on certain dropdowns. I've managed this ok so far, however I've run into an issue where one dropdown box in cell E30 is conditional.

[Updated] What I need is: IF E30 = 'No' AND E6 = 'VIC'
Then Rows 1:45 are not hidden AND Rows 46:81 are hidden
IF E30 = 'Yes' AND E6 = 'VIC'
Then Rows 1:33 are not hidden AND Rows 34:81 are hidden
IF E30 = 'No' AND E6 = 'OTHER'
Then Rows 1:33 and 64:81 are not hidden AND Rows 34:63 are hidden
IF E30 ='Yes' AND E6 ='OTHER'
Then Rows 1:33 are not hidden AND Rows 34:81 are hidden

The following is the code I have so far. The first group working as designed, however the second is the above partial code I have a problem with. Hope this makes sense, thank you for all assistance in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
        ActiveSheet.Activate
        If Not Application.Intersect(Range("E19"), Range(Target.Address)) Is Nothing Then
                Select Case Target.Value
                
                Case Is = "NO":     Rows("34:81").EntireRow.Hidden = True
                                    Rows("1:22").EntireRow.Hidden = False
                                    Rows("23:33").EntireRow.Hidden = False
                Case Is = "YES":    Rows("23:81").EntireRow.Hidden = True
                                    Rows("1:22").EntireRow.Hidden = False
                                    
                End Select
        End If
        
        ActiveSheet.Activate
        If Not Application.Intersect(Range("E30"), Range(Target.Address)) Is Nothing Then
                Select Case Target.Value
                                                              
                                               
                Case Is = "YES":    Rows("34:81").EntireRow.Hidden = True
                                    Rows("1:33").EntireRow.Hidden = False
                
                Case Is = "NO":     Rows("34:63").EntireRow.Hidden = True
                                    Rows("1:33").EntireRow.Hidden = False
                                    Rows("64:81").EntireRow.Hidden = False
                                                     
                End Select
        End If

End Sub

CodePudding user response：

A Worksheet Change: Hide Rows Based On Multiple Criteria

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("E19"), Target) Is Nothing Then
        Select Case UCase(CStr(Range("E19").Value))
        Case "YES"
            Rows("1:22").Hidden = False
            Rows("23:81").Hidden = True
        Case "NO"
            Rows("1:22").Hidden = False
            Rows("23:33").Hidden = False
            Rows("34:81").EntireRow.Hidden = True
        'Case Else
        End Select
    End If

'Yes
'IF E30 = 'Yes' AND E6 = 'VIC'
'Then Rows 1:33 are not hidden AND Rows 34:81 are hidden
'IF E30 ='Yes' AND E6 ='OTHER'
'Then Rows 1:33 are not hidden AND Rows 34:81 are hidden

'No
'IF E30 = 'No' AND E6 = 'VIC'
'Then Rows 1:45 are not hidden AND Rows 46:81 are hidden
'IF E30 = 'No' AND E6 = 'OTHER'
'Then Rows 1:33 and 64:81 are not hidden AND Rows 34:63 are hidden
    
    If Not Intersect(Range("E6,E30"), Target) Is Nothing Then
        Select Case UCase(CStr(Range("E30").Value))
        Case "YES"
            Select Case UCase(CStr(Range("E6").Value))
            Case "VIC", "OTHER"
                Rows("1:33").Hidden = False
                Rows("34:81").Hidden = True
            'Case Else
            End Select
        Case "NO"
            Select Case UCase(CStr(Range("E6").Value))
            Case "VIC"
                Rows("1:45").Hidden = False
                Rows("46:81").Hidden = True
            Case "OTHER"
                Rows("1:33").Hidden = False
                Rows("34:63").Hidden = True
                Rows("64:81").Hidden = False
            'Case Else
            End Select
        'Case Else
        End Select
    End If

End Sub

CodePudding user response：

Here is the code - Make sure to replace Sheet1 with your sheet.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$6" Or Target.Address = "$E$30" Then

    If Sheet1.Range("E30").Value = "No" And Sheet1.Range("E6").Value = "VIC" Then
    
        Sheet1.Range("A1:A45").Rows.EntireRow.Hidden = False
        Sheet1.Range("A46:A81").Rows.EntireRow.Hidden = True
    
    ElseIf Sheet1.Range("E30").Value = "Yes" And Sheet1.Range("E6").Value = "VIC" Then
        
        Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
        Sheet1.Range("A34:A81").Rows.EntireRow.Hidden = True
    
    ElseIf Sheet1.Range("E30").Value = "No" And Sheet1.Range("E6").Value = "OTHER" Then
        
        Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
        Sheet1.Range("A64:A81").Rows.EntireRow.Hidden = False
        Sheet1.Range("A34:A63").Rows.EntireRow.Hidden = True
    
    ElseIf Sheet1.Range("E30").Value = "Yes" And Sheet1.Range("E6").Value = "OTHER" Then
        
        Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
        Sheet1.Range("A34:A81").Rows.EntireRow.Hidden = True
    
    End If