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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
Could you be a bit clearer on exactly what should be filtered please?

You say that you're trying to filter based on level (field 5) and level date (field 6) but your code is filtering the dates in field 3. Does this mean that the date filter is to be applied on both date columns?

Also, what exactly are you trying to do with the field 5 criteria? "*<>2*" do you actually have <>2 in some cells, or should it be "<>*2*"?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
This is extremely confusing!!
My table below starts on row 5.
But the table shows five rows above with the headings on row 6
.. the code appears to be applying Autofilter from row 10 ( ActiveSheet.Range("10:10").AutoFilter )
.. but then from row 7 ( Range("7:7").AutoFilter ... )

Where does the AutoFilter start (row 5, 6, 7, 10 or somewhere else)?
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Alright well for starters sorry for apparently confusing everyone. I inadvertently pasted the wrong code it should have been this:

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

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

The reason for this Range("10:10").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="*<>2*" is the data set could have the #2 or test value of 2 so was trying to solution both but that part has always failed so I sort have been living with it not yielding the correct filter

Data should start at row 10 and filter on row 10. ultimately looking for:

Field 3: if doesn't = today's date to show (format 20200824)
Field 6: if doesn't = today's date to show (format 24-Aug-20)
Field 5: if doesn't = value 2 (number or text)
Field 7: If = AA or DD only.

When all said and done. i should have two records AA and the first DD listed above.
 

jasonb75

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

ADVERTISEMENT

The only way that I know of to do this is with a logical test in an additional column.

I've tried a few things but not getting the expected output, I can see what it should be but can't get it right for some reason. Going to put it down for a while and have another look later. Hopefully @Peter_SSs will return with some fresh ideas before I lose what little sanity I have left 🤪
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
This is the part where I'm getting the logic wrong, looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?

Bear in mind that we're assuming today is the 24th in order to keep the test consistent.

25.08.20.xlsm
EFG
10LevelLevel Dateindicator
1100AA
1200BB
1310BB
14123/08/2020DD
15124/08/2020DD
16223/08/2020DD
17224/08/2020DD
Sheet24
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

This is the part where I'm getting the logic wrong, looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?

Bear in mind that we're assuming today is the 24th in order to keep the test consistent.

25.08.20.xlsm
EFG
10LevelLevel Dateindicator
1100AA
1200BB
1310BB
14123/08/2020DD
15124/08/2020DD
16223/08/2020DD
17224/08/2020DD
Sheet24
Row 11 and Row 16 looking to show. basically looking for the level to be 2 AND level date to be today. so the and is driving me nuts. and also getting it so the 2 can be either value or text.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Row 11 and Row 16 looking to show. basically looking for the level to be 2 AND level date to be today. so the and is driving me nuts. and also getting it so the 2 can be either value or text.
wait your rows 14 and 15 should be CC
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
wait your rows 14 and 15 should be CC
No, I deliberately changed DD to DD and changed E17 from 1 to 2 in order to give 4 different combinations of Level and Level date whilst using DD as a constant control value, and asked
looking at the last 4 rows in this edited example, which ones should be shown after the filter is applied?
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
No, I deliberately changed DD to DD and changed E17 from 1 to 2 in order to give 4 different combinations of Level and Level date whilst using DD as a constant control value, and asked
Ahhh you did say that yes so with DD then it should be rows 10, 14, 16
 

Watch MrExcel Video

Forum statistics

Threads
1,119,087
Messages
5,576,035
Members
412,694
Latest member
Deaf1Too
Top