VBA help based on drop down and blanks

[pleasehelpthischick]

Hello!
I am trying to write some VBA code to do the following:

User selects yes or no from a drop down list in E3
(the yes no values are in column B)

If yes, then I only want to show rows in column B that have a value of yes AND row c that have a value of yes.
i would like all other rows to be hidden

i would also like the opposite to happen, if they select no, only rows with the value of no in B and C should be shown and the rest hidden.

Can someone please help?

 

[James006]

Hi,

Before getting into VBA, do you have this solution fully operational with the standard Filter ?

 

[snjpverma]

If E3 is yes and B3 is yes but C3 is No, then what should happen? Will that row be hidden or not?
Also, what all data can column b and c contain? Yes, No, blanks and other? All 4 of these? Or only yes and no without blanks or other data in between?

 

[pleasehelpthischick]

If E3 is yes and B3 is yes but C3 is No, then what should happen? Will that row be hidden or not?

If E3 is no, B3 is no, C3 would also be no.
If E3 is no, then anything in column B and C that is no, would be hidden. Columns B and C will either both be yes or both no.

 

[snjpverma]

Try this in a copy of your workbook

Sub FilterRows()
    Dim i As Long
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim filterValue As String
   
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    filterValue = ws.Range("E3").Value
   
    For i = 2 To lastRow
        If ws.Cells(i, "B").Value = filterValue Then
            ws.Rows(i).EntireRow.Hidden = False
        Else
            ws.Rows(i).EntireRow.Hidden = True
        End If
    Next i
End Sub

 

[snjpverma]

Or this in the worksheet module where you want the changes to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$3" Then
        FilterRows
    End If
End Sub

Sub FilterRows()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim filterValue As String
    filterValue = ws.Range("E3").Value
    
    If filterValue <> "" Then
        ws.Range("B:B").AutoFilter Field:=1, Criteria1:=filterValue
    Else
        ws.ShowAllData
    End If
End Sub

 

[pleasehelpthischick]

Or this in the worksheet module where you want the changes to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$3" Then
        FilterRows
    End If
End Sub

Sub FilterRows()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim filterValue As String
    filterValue = ws.Range("E3").Value
   
    If filterValue <> "" Then
        ws.Range("B:B").AutoFilter Field:=1, Criteria1:=filterValue
    Else
        ws.ShowAllData
    End If
End Sub

Neither of these snippets of code are working. If I select yes or no from the drop down list in E3, nothing is filtered, nothing happens.
What I would like:
If I select no and b and c contain yes, that row needs to be hidden.
If I select yes and b and c contain no, that row needs to be hidden.

 

[pleasehelpthischick]

Neither of these snippets of code are working. If I select yes or no from the drop down list in E3, nothing is filtered, nothing happens.
What I would like:
If I select no and b and c contain yes, that row needs to be hidden.
If I select yes and b and c contain no, that row needs to be hidden.

I tried again and getting this error:

 

[snjpverma]

@pleasehelpthischick , unfortunately since I am out on a vacation for around 12 days without a laptop, I won't be able to check the issue.
By the way, you are running the code from the same sheet, right?
Also, incase of the code given in Code 6, where have you inserted the code?
Hopefully someone with a good knowledge of VBA will be able to help you out soon