Hi Tess,

The following macro copies your data to a new sheet,Autofilters twice in a
column since you have three criteria, flags the matches, loops through the
three columns and deletes the rows that don't match. If you want to do this
on the original sheet comment out or delete the two statements as noted. Put
your criteria in where I show three different text entries.

CHORDially,
Art Farrell

Option Explicit

Sub AutoFcontains()
Dim n As Long
Dim rng1 As Range

Application.ScreenUpdating = False
'remove following two statements if you want to delete rows on active sheet.
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = "DataFilter"

Columns(1).Insert Shift:=xlToRight
Range("A1").Formula = "1"
Set rng1 = ActiveSheet.UsedRange
n = 1
Do
rng1.AutoFilter Field:=n + 3, Criteria1:="=*AAA*", _
Operator:=xlOr, Criteria2:="=*BCD*"
Range(Cells(1, 1), Cells(1, n + 3).End(xlDown).Offset(0, -n -
2)).FillDown
Selection.AutoFilter
rng1.AutoFilter Field:=n + 3, Criteria1:="=*YMGT*"
Range(Cells(1, 1), Cells(1, n + 3).End(xlDown).Offset(0, -n -
2)).FillDown
n = n + 1
Selection.AutoFilter
Loop Until n = 4
rng1.AutoFilter Field:=1, Criteria1:="="
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).EntireRow.Delete
Selection.AutoFilter
Columns(1).Delete
End Sub

"tess457" wrote in message
...

Hello,
I am trying to filter out a large amount of data, currently I am only
searching one column("C:C"), but for another function, I am needed to
filter through 3 columns (C,D,E). I also need to revise my code, so
that if the cell contains any part of the string I am looking for, it
should filter it out(keep the row), and if not, it should delete the
row. Right now, this code will search col c, and if that row EQUALS my
search variable (FVar), it will keep the row. (set it to true).

Function Filter4(FVar)
Dim rnData As Range
Dim iRows As Integer
Dim iColumns As Integer

Application.ScreenUpdating = False
Range("A1").Select
Set rnData = ActiveSheet.UsedRange
Let iRows = rnData.CurrentRegion.Rows.count
Let iColumns = 6

Cells(1, iColumns).FormulaR1C1 = "Sort"
Cells(2, iColumns).Select
Selection.FormulaR1C1 = "=OR(RC[-3]={""" & FVar & """})"
Selection.Copy Destination:=Range(Cells(3, iColumns), Cells(iRows,
iColumns))

With rnData
AutoFilter Field:=iColumns, Criteria1:="False"
Offset(1, 0).Resize(.Rows.count - 1, .Columns.count) _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
AutoFilter
End With

rnData.Columns(iColumns).Delete
Application.ScreenUpdating = True

End Function

Would anyone be able to help me out in order to be able to search
through 3 columns, and even if the cell contains part of the search
variable, it will filter it out??? thanks alot for any suggestions
that you may have!!


--
tess457
------------------------------------------------------------------------
tess457's Profile:
http://www.excelforum.com/member.php...o&userid=13938
View this thread: http://www.excelforum.com/showthread...hreadid=262061