Autofilter Criteria >left(e2,2) or <left(e2,2) 1

[APElliott]

Hi There,

Can anyone help me with some code to perform an autofilter?

I'm trying to filter all items in a column that are greater or less than the first 2 characters in cell e2.

I've tried the following but it won't work!

Selection.AutoFilter Field:=7, Criteria1:=">left(e2,2)", Operator:=xlOr, _
Criteria2:="<left(e2,2)"

Thanks,

Andrew

 

[ETID]

try this one...

Selection.AutoFilter Field:=7, Criteria1:"=>"&left(range("e2"),2)", Operator:=xlOr, _
Criteria2:"=<"&left(range("e2"),2)

 

[APElliott]

Cheers ETID,

I got a Syntax error with the code. However, I've amended to this and it does what I want. Thankyou!

Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="<>" & Left(Range("e2"), 2)

 

[APElliott]

Hi Again,

Problem! The code won't work on my data field when it's been pasted from another document.

It only selects the required data if I change the filter to a 'Does Not Contain Criteria' as below:

Selection.AutoFilter Field:=7, Criteria1:="<>*20*"

'20' being the first 2 characters of cell E2

Thankyou,

Andrew

 

[ETID]

how about...

crit_1="<>*"&left(trim(range("e2")),2)&"*"

Selection.AutoFilter Field:=7, Criteria1:=crit_1

 

[APElliott]

Brill,

Works a treat!

Cheers,

Andrew