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
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
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.
Yeah so adding that filter works by filtering out the #2 but then im left behind with the text of 2. I can redraft up a test scenario seeing my data is too long to paste here.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,601
Office Version
  1. 365
Platform
  1. Windows
That was my bad, I thought I had used text and number when I ran a quick test, but I only had number. This is filtering both for me,
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:="<>IA"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D5").Value2
    .AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlAnd, Criteria2:="<>*2"
    .AutoFilter Field:=17, Criteria1:="TRUE"
End With
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
That was my bad, I thought I had used text and number when I ran a quick test, but I only had number. This is filtering both for me,
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:="<>IA"
    .AutoFilter Field:=3, Criteria1:="<>" & Range("D5").Value2
    .AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlAnd, Criteria2:="<>*2"
    .AutoFilter Field:=17, Criteria1:="TRUE"
End With
All good! works! Thanks sir.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,813
Messages
5,574,483
Members
412,596
Latest member
nickthebizz
Top