Filtering Data
 

Hello,
I am trying to filter out a large amount of data, currently I am onl
searching one column("C:C"), but for another function, I am needed t
filter through 3 columns (C,D,E). I also need to revise my code, s
that if the cell contains any part of the string I am looking for, i
should filter it out(keep the row), and if not, it should delete th
row. Right now, this code will search col c, and if that row EQUALS m
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 searc
through 3 columns, and even if the cell contains part of the searc
variable, it will filter it out??? thanks alot for any suggestion
that you may have!

--
tess45
-----------------------------------------------------------------------
tess457's Profile: http://www.excelforum.com/member.php...fo&userid=1393
View this thread: http://www.excelforum.com/showthread.php?threadid=26206

Filtering Data
 

Tess:

Here is a set of macros I use to search through a spreadsheet for a text
string (can be numbers or letters; does not have to be a complete word). If
the string is NOT found, the row is hidden (not deleted, in case you want to
unhide it and search for something else later). The second macro allows the
search to increment down a filtered list. I hope these work for you.

Ed

Sub SearchRows()

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long
Dim HereRow As Long
Dim rngFound As Range
Dim cntFound As Long

cntFound = 0

MyTarget = ""
MyTarget = Application.InputBox("What text are you searching for?")
If MyTarget = "" Or MyTarget = "False" Then GoTo Bye

Range("B1").Activate

Application.ScreenUpdating = False

Do

' Increment to next visible cell
AAIncrement3

If ActiveCell.Text = "" Then GoTo Bye
HereRow = ActiveCell.Row
If HereRow = thisRow Then GoTo Bye

thisRow = ActiveCell.Row

' Change column letters to set a different search range
Set rngSearch = Range("A" & thisRow & ":T" & thisRow)
rngSearch.Select

On Error Resume Next
Set rngFound = Selection.Find(What:=MyTarget, _
MatchCase:=False)
On Error GoTo 0

If rngFound Is Nothing Then
Range("A" & thisRow).EntireRow.Hidden = True
Else
cntFound = cntFound + 1
End If

Set rngSearch = Nothing
Set rngFound = Nothing
Range("B" & thisRow).Activate

Loop

Bye:
Application.ScreenUpdating = True

Range("A1").Select
MsgBox "Found " & cntFound & " rows that contained " & MyTarget
End Sub

Sub AAIncrement3()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
If Not ActiveSheet.AutoFilterMode Then
Range("A1").Select
Selection.AutoFilter
End If
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = Intersect(rng, rng.SpecialCells(xlVisible))
On Error GoTo 0
If Not rng1 Is Nothing Then
If rng1(1).Row = ActiveCell.Row Then
ActiveCell.Offset(1, 0).Select
Else
rng1(1).Select
End If
Else
rng(rng.Count).Offset(1, 0).Select
End If
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

Filtering Data
 

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