VBA AutoFilter off Text and Cell Value Based

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi, I have two scenarios where I am trying to solution and cant seem to get the results I was expecting

Trying to filter data based on a field not equaling 2 but the data is in text on my excel data and also filtering out a date based off a cell value

D5 =TEXT(TODAY(),"YYYYMMDD")
Today is 07/11/20 for reference

Range("10:10").AutoFilter Field:=5, Criteria1:="<>AB"
Range("10:10").AutoFilter Field:=3, Criteria1:="<>2"
Range("10:10").AutoFilter Field:=4, Criteria1:="<>Range(D5)"


DateDate#DateView Indicator
20200710​
20200710​
0
0​
AB
20200712​
20200712​
1
20200712​
AA
20200712​
20200712​
2
20200712​
AC
20200712​
20200712​
0
0​
AD
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
 Range("10:10").AutoFilter Field:=4, Criteria1:="<>"&Range("D5")
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Try
VBA Code:
 Range("10:10").AutoFilter Field:=4, Criteria1:="<>"&Range("D5")

Fluff - As usual you are the best. The one piece that still isn't working is my filter off of "2"' I think the problem resides with the data itself kicking out in text format?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Use "<>*2*"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
390
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Ok so of course i have a variable. in that column I may have #2 and text 2. if i try this it gives me all Numbers.

Range("10:10").AutoFilter Field:=5, Criteria1:="<>2", Operator:=xlOr, Criteria2:="<>*2*"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
The best option is to clean up your data so that you either have numbers or text, rather than a mixture.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,254
Messages
5,576,977
Members
412,755
Latest member
vintage88
Top