Advanced Filtering

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
733
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
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
 
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.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top