PDA

View Full Version : [SOLVED] Filter based on 3 OR conditions and cut paste the same on different sheet



shan
11-18-2015, 09:25 PM
Hello Everybody,

Looking for a macro code which will Filter the data of a column based on 3 OR conditions and cut paste the same in new sheet named "Kont".

I have recorded below code but unable to add third OR condition i.e. "=*GHI*".
Kindly assist me on the same.


ActiveSheet.Range("$A$1:$R$7322").AutoFilter Field:=9, Criteria1:="=*ABC*" _
, Operator:=xlOr, Criteria2:="=*DEF*"


Regards,
Shan

mancubus
11-19-2015, 02:22 AM
Sub vbax_54323_AutoFilter_Three_Or_More_Criteria()

Dim FilterCrit
Dim i As Long

FilterCrit = Array("*ABC*", "*DEF*", "*GHI*")

With Worksheets("MySheet") 'change MySheet to suit
For i = LBound(FilterCrit) To UBound(FilterCrit)
.Cells(1).AutoFilter Field:=9, Criteria1:="=" & FilterCrit(i)
If .AutoFilter.Range.Rows.Count > 1 Then 'check if there are any filtered rows
'code with filtered rows here

'example: copy and delete filtered rows
'.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Copy Destination:=Worksheets("MySheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
'.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete

End If
Next
End With

End Sub

Bob Phillips
11-20-2015, 07:51 AM
I would so it by adding a column and building a formula that I filter on


With ActiveSheet

.Columns(10).Insert
.Range("J1").Value = tmp
.Range("$J$2:$J$7322").Formula = "=SUM(COUNTIF(I2,{""*ABC*"",""*DEF*"",""*GHI*""}))>0"
.Range("$A$1:$R$7322").AutoFilter Field:=10, Criteria1:="TRUE"
'do your stuff

.Columns(10).Delete 'tidy-up afterwards
End With

shan
11-22-2015, 09:30 PM
Thank you Sir,

The code is working as desired.