Advanced Filtering

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.


202008248/24/2020
24-Aug-20​
Desc 1Desc 2Date 1Date 2LevelLevel Dateindicator
1A
20200823​
20200823​
0
0​
AA
2B
20200823​
0​
0
0​
BB
3C
20200823​
0​
0
0​
BB
4D
20200823​
20200823​
1
23-Aug-20​
CC
5E
20200823​
20200823​
1
24-Aug-20​
CC
6F
20200823​
20200823​
2
23-Aug-20​
DD
7G
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows
Taking that into consideration, I can't see any situation where the "<>*2*" criteria is necessary, this works fine with the example.
VBA Code:
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

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
So Rows 11, 14, 15, 16. I would need the "2" to capture row 15, correct? apologies missed that before
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

in my data set i have one field that shows the 2 as a text
That's easy enough to fix
VBA Code:
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

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
That's easy enough to fix
VBA Code:
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

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

Code:
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

VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,183
Messages
5,576,574
Members
412,734
Latest member
rubinrods
Top