CODEWITHSUNDEEP
excelvba
Steve7788
Steve7788

Reputation: 21

Hide Rows based on Multiple Criteria

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

I need:
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 is working as designed.
The second is the above partial code I have a problem with.

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

Upvotes: 2

Views: 392

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 1

Praveen Behera
Praveen Behera

Reputation: 444

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

Upvotes: 0

Related Questions