Filter based on list of words

[stolie]

HI

I have done quite a bit of searching and cant quite find the answer.

I wish to perform a "contains" filter on a column which contains a string of text.

This will be looped using a list of words as the "contains" reference.

The advanced filter doesnt do what i am after.

I realise i can do this in a crude version using mulitples of the below,

ActiveSheet.Range("$A$1:$AA$200").AutoFilter Field:=6, Criteria1:= _
"=word", Operator:=xlAnd

But my list is huge, and surely there is a way to get it to read from a list and fitler accordingly?

Also to paste the corresponding row where the word is found into a new sheet would top it off!

Thanks again

 

[John Davis]

HI

I have done quite a bit of searching and cant quite find the answer.

I wish to perform a "contains" filter on a column which contains a string of text.

This will be looped using a list of words as the "contains" reference.

The advanced filter doesnt do what i am after.

I realise i can do this in a crude version using mulitples of the below,

ActiveSheet.Range("$A$1:$AA$200").AutoFilter Field:=6, Criteria1:= _
"=word", Operator:=xlAnd

But my list is huge, and surely there is a way to get it to read from a list and fitler accordingly?

Also to paste the corresponding row where the word is found into a new sheet would top it off!

Thanks again

How large is the list?

 

[hiker95]

stolie,

Are you looking to copy rows of data based on some text into separate worksheets based on the text?

Or, into one workhseet for all the searched text?

You may be able to create an array of the search text and cycle thru and autofilter.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[John Davis]

How large is the list?

Does this do you any good, or give you any ideas?

Sub Stolie()
Dim x As Variant
Dim i As Variant
Dim lr As Long
Dim lr2 As Long

lr = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Sheet3")

x = Array("WORD1", "WORD2", "WORD3", "WORD4")

For i = LBound(x) To UBound(x)

lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

    .Range("A2:AA" & lr).AutoFilter Field:=6, Criteria1:=x(i)
    .Range("A2:AA" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet4").Range("A" & lr2)
    .Range("A2:AA" & lr).AutoFilter

Next i

End With

End Sub