Advanced Filtering

[ItalianPlatinum]

Hello - Having a roadblock on filtering using VBA. I have a simple dataset where I need two columns criteria to be met. The issue i am having is. if level date is 2 but the level date is NOT 8/24 i want it to show but my filter is filtering it out. How can I get the filtering to be smart enough to say if both are met to filter it out, but only if both are met. My table below starts on row 5.

			20200824		8/24/2020
			24-Aug-20​
Desc 1	Desc 2	Date 1	Date 2	Level	Level Date	indicator
1	A	20200823​	20200823​	0	0​	AA
2	B	20200823​	0​	0	0​	BB
3	C	20200823​	0​	0	0​	BB
4	D	20200823​	20200823​	1	23-Aug-20​	CC
5	E	20200823​	20200823​	1	24-Aug-20​	CC
6	F	20200823​	20200823​	2	23-Aug-20​	DD
7	G	20200823​	20200823​	1	24-Aug-20​	DD

Sub TEST2()
If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("10:10").AutoFilter

Range("7:7").AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
Range("7:7").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="*<>2*"
Range("7:7").AutoFilter Field:=3, Criteria1:="<>" & Range("D6")
Range("7:7").AutoFilter Field:=3, Criteria1:="<>" & Range("D5")

End Sub

 

[jasonb75]

Row 10?

 

[ItalianPlatinum]

Row 10?

LOL 11....

 

[jasonb75]

Taking that into consideration, I can't see any situation where the "<>*2*" criteria is necessary, this works fine with the example.

Sub TEST2()
With Range("10:10")
    .AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D6").Value2, Operator:=xlAnd, Criteria1:="<>" & Range("D5").Value
    .AutoFilter Field:=6, Criteria1:="<>" & Range("F5").Value2
End With
End Sub

To expand beyond this, I would need an example of rows that are incorrectly shown or hidden in a format similar to the one that I used earlier.

 

[ItalianPlatinum]

So Rows 11, 14, 15, 16. I would need the "2" to capture row 15, correct? apologies missed that before

 

[jasonb75]

Now it makes more sense, I thought it was something like that but I had it backwards which explains what it wasn't making sense.

This code will add a formula to column H that identifies the rows to be filtered based on the Level and Level date, the other columns are still filtered independently.

Sub TEST2()
Range("H10").Value = "Filter"
Range("H11:H" & Cells(Rows.Count, 7).End(xlUp).Row).Formula = "=IF(E11=2,F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D6").Value2, Operator:=xlAnd, Criteria1:="<>" & Range("D5").Value
    .AutoFilter Field:=8, Criteria1:="TRUE"
End With
End Sub

 

[ItalianPlatinum]

Now it makes more sense, I thought it was something like that but I had it backwards which explains what it wasn't making sense.

This code will add a formula to column H that identifies the rows to be filtered based on the Level and Level date, the other columns are still filtered independently.

Sub TEST2()
Range("H10").Value = "Filter"
Range("H11:H" & Cells(Rows.Count, 7).End(xlUp).Row).Formula = "=IF(E11=2,F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D6").Value2, Operator:=xlAnd, Criteria1:="<>" & Range("D5").Value
    .AutoFilter Field:=8, Criteria1:="TRUE"
End With
End Sub

NICE works for all scenarios except one and I knew this would come back to haunt me. in my data set i have one field that shows the 2 as a text. if i cant solution it via the filter I can try to dissect the vba kicking out the data to add a step change the column to number format

 

[jasonb75]

in my data set i have one field that shows the 2 as a text

That's easy enough to fix

Sub TEST2()
Range("H10").Value = "Filter"
Range("H11:H" & Cells(Rows.Count, 7).End(xlUp).Row).Formula = "=IF(ISNUMBER(SEARCH(2,E11)),F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D6").Value2, Operator:=xlAnd, Criteria1:="<>" & Range("D5").Value
    .AutoFilter Field:=8, Criteria1:="TRUE"
End With
End Sub

 

[ItalianPlatinum]

That's easy enough to fix

Sub TEST2()
Range("H10").Value = "Filter"
Range("H11:H" & Cells(Rows.Count, 7).End(xlUp).Row).Formula = "=IF(ISNUMBER(SEARCH(2,E11)),F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=7, Criteria1:="AA", Operator:=xlOr, Criteria2:="DD"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D6").Value2, Operator:=xlAnd, Criteria1:="<>" & Range("D5").Value
    .AutoFilter Field:=8, Criteria1:="TRUE"
End With
End Sub

WAHOO. Nice work on this Jason. Much appreciated. I just did a test dry run and it works. Going to apply it across all my data tonight

 

[ItalianPlatinum]

WAHOO. Nice work on this Jason. Much appreciated. I just did a test dry run and it works. Going to apply it across all my data tonight

Jason - I got one variable that is causing some issue. if i have some criteria in Column E = to 2 (text or value), i also want to exclude it when using this filter. I am not sure how to exclude and have it exclude the
#2 and the text 2. i thought of using this, but it giving trouble when i get #2 or test 2

Range("Q10").Value = "Filter"
Range("Q11:Q" & Cells(rows.count, 16).End(xlUp).row).Formula = "=IF(ISNUMBER(SEARCH(1,E11)),F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=10, Criteria1:="<>IA"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D5").Value2
    .AutoFilter Field:=5, Criteria1:="<>2"
    .AutoFilter Field:=17, Criteria1:="TRUE"
End With

Original

Range("Q10").Value = "Filter"
Range("Q11:Q" & Cells(rows.count, 16).End(xlUp).row).Formula = "=IF(ISNUMBER(SEARCH(1,E11)),F11<>$F$5,TRUE)"
With Range("10:10")
    .AutoFilter
    .AutoFilter Field:=10, Criteria1:="<>AA"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D5").Value2
    .AutoFilter Field:=17, Criteria1:="TRUE"
End With

 

[jasonb75]

I'm not following. The extra line that you've added looks correct based on what you say that you want to do.

Best guess at the moment it that the filter in that column is conflicting with one of the other columns but I would need to see a sample with some rows that are not being filtered correctly in order to establish the actual cause.