Autofilter problem

[M8rix]

I am using the following code to find PTS in column 2 and 3, the problem is it is only finding PTS if it appears in column 2 & 3 on the same row else you do not see them if PTS is just in column 2 or column 3.

ActiveSheet.Range("$A$6:$IP$340").AutoFilter Field:=2, Criteria1:= _
        "=PTS*", Operator:=xlOr
    ActiveSheet.Range("$A$6:$IP$340").AutoFilter Field:=3, Criteria1:= _
        "=PTS*", Operator:=xlOr

Is there a simple fix some one can provide that will display all rows containing PTS whether it is in column 2 or column 3 else i am having to run 2 seperate filters


Many thanks in advance

Mark

[mdmackillop]

Hi Mark
You can't Autofilter xlOr on two columns, You can either use advance filter or add a helper column combining the data from Columns 2 & 3 then filter for "=*PTS*"

[M8rix]

Many thanks

Is there code to do this i.e. amend this code:

ActiveSheet.Range("$A$6:$IP$340").AutoFilter Field:=2, Criteria1:= _
"=PTS*", Operator:=xlOr
ActiveSheet.Range("$A$6:$IP$340").AutoFilter Field:=3, Criteria1:= _
"=PTS*", Operator:=xlOr

Or will i need to actually go through the process of using the advanced filter?

[mdmackillop]

Is there code to do this i.e. amend this code

You can't amend that code, but this will add and filter on a helper column

Sub Test()
With ActiveSheet
For Each cel In .Range("IQ6:IQ340")
    cel.Value = cel.Offset(, -249) & cel.Offset(, -248)
Next
.Range("$IQ$6:$IQ$340").AutoFilter Field:=1, Criteria1:="=*PTS*"
End With
End Sub

[M8rix]

cheers

I was hoping to avoid the 'helper' column option, i will have a go at the advanced filter.
Many thanks for your help

M

[p45cal]

i will have a go at the advanced filter.

See attached.

(Instead of the formula:

="=PTS*"

you could have:

'=PTS*

or, as it turns out, just:

PTS

in the advanced criteria cells.)