Copy row IF cell contains specific word

[mvreeswijk]

Dear all,

I have a problem concerning the following: I have an Excel file containing 20 colums and 12000 rows. I would like to filter the data based on certain words. As an example, IF column 3, 4, 5 or 6 contains the word "Bank" it should select the entire row and copy the contents of the entire row into a new sheet.

It should thus go through all 12.000 rows and when it finds the word "bank" e.g. "bank of indonesia" or "the royal bank" it should copy everything in that row and copy it in e.g. a new worksheet named "internal".

Can anyone help me on this?

Thanks in advance.

Mark Vreeswijk

 

[Norie]

Try this:

Sub BankMove()
Const strTest = "Bank"
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim rngCells As Range
Dim rngFind As Range
    
    Set wsSource = ActiveSheet
    
    NoRows = wsSource.Range("A65536").End(xlUp).Row
    DestNoRows = 1
    Set wsDest = ActiveWorkbook.Worksheets.Add
        
    For I = 1 To NoRows
    
        Set rngCells = wsSource.Range("C" & I & ":F" & I)
        
        If Not (rngCells.Find(strTest) Is Nothing) Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            
            DestNoRows = DestNoRows + 1
        End If
    Next I
End Sub

 

[mvreeswijk]

Thank you very much for your reply. I do not know how good you are, but this is amazing, so quick! Just one additional question:

How to alter the script when I want to filter on more than one word e.g. Bank and other words suchs as Firm or KLM? In other words, if they contain any of those words it needs to be copied.

Thanks in advance,

Mark Vreeswijk

 

[Norie]

You could try changing this

If Not (rngCells.Find(strTest) Is Nothing) Then

to

If Not (rngCells.Find(strTest1) Is Nothing) AND Not(rngCells.Find(strTest2) Is Nothing) Then

where strText1 and strText2 contain the words you are looking for.

You could add more 'Not (rngCells.Find(strTest1) Is Nothing' statements.

There might be a better way to do it.

 

[Norie]

In fact try this

Sub BankMove()
Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean
    
    strArray = Array("bank", "KLM", "firm")
    
    Set wsSource = ActiveSheet
    
    NoRows = wsSource.Range("A65536").End(xlUp).Row
    DestNoRows = 1
    Set wsDest = ActiveWorkbook.Worksheets.Add
        
    For I = 1 To NoRows
    
        Set rngCells = wsSource.Range("C" & I & ":F" & I)
        Found = False
        For J = 0 To UBound(strArray)
            Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
        Next J
        
        If Found Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            
            DestNoRows = DestNoRows + 1
        End If
    Next I
End Sub

It will copy any row that contains any of the words in strArray.

 

[mahen77]

Hi,

You can also try the filter, only you have to do the custom filter with "Contains" option and you will get your result.

You will then copy and paste it to another sheet.

Thanks
Mahen..

 

[mvreeswijk]

Thank you very much for the script update again! It seems to work, awesome! What should I add if I want to delete the rows in the original sheet that are being copied to the new sheet?

Thanx,

Mark

 

[PhilThomas]

IS there any way to put this sort of thing into a cell formula or does it have to be a macro? Which is what I assume the code is?

I am trying to do the same thing but wanted it to be dynamic rather than having to run a macro each time.

Eg. Col A = Val available, not available, ready etc.
Col B = 1 if not avail, 2 if available, 3 if ready etc.

Unfortunately some of the answers in Col A contain things like available TT which I need to match with a "contains available" statement after testing for "not available".

Phil

 

[Theta Sigma]

Yay! I'm looking to do pretty much the same thing as mvreeswijk.
I want to use the scripts but where exactly do I enter the script into Excel 2007??

 

[netizenn]

I guess I was looking for the same and this surely is the trick...

Example:
Filter Column A using set of multiple keywords in Column B

Try this link: - http://www.mrexcel.com/forum/excel-questions/85439-find-specific-text.html


Thanks!
netizenn