PDA

View Full Version : [SOLVED] Autofilter problem



M8rix
03-22-2017, 06:36 AM
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
03-22-2017, 09:46 AM
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
03-23-2017, 02:40 AM
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
03-23-2017, 02:55 AM
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
03-23-2017, 05:03 AM
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
03-23-2017, 04:42 PM
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.)