Autofilter problem

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.

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

---

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

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*"

Many thanks

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

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?

Quote:

---

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

Code:

---

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

---

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

Quote:

---

Originally Posted by M8rix

i will have a go at the advanced filter.

---

See attached.

(Instead of the formula:

Code:

---

="=PTS*"

---

you could have:

Code:

---

'=PTS*

---

or, as it turns out, just:

Code:

---

PTS

---

in the advanced criteria cells.)