Delete column a if keyword is in column b

[fernandosaez]

I work for an export business and we have over 4,000 products and over 10,000 people we export to around south america.

I was presented a list with the contact info in the left and on the right a paragraph that contains the items that have been purchased before in the past.

Can you guys help me with a code that will delete a row (a and b) if a keyword or words is present in b? So if it contains the word trumpet or horn it ill delete that whole row.

THANKS FOR YOUR HELP!

Looks like this
https://dl.dropbox.com/u/9443/help please.xlsx

 

[RJPotts]

http://www.mrexcel.com/forum/excel-...applications-delete-row-based-cell-value.html

Best regards
Richard

 

[Michael M]

Maybe something like this

Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 2 Step -1
    If InStr(Range("B" & r), "trumpet") Or InStr(Range("B" & r), "horn") Then
        Rows(r).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub

 

[fernandosaez]

Sorry

So I have a column A and B

I went to macros, entered a name, clicked create. Copy and pasted all the text and then ran it. Nothing got erased. Am I doing something wrong? Some of the text is separated like this car-house could that be why? In side B, it isn't as singled word but a block with text and sentences. Thanks!

 

[Michael M]

OK, try these instructions

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

 

[fernandosaez]

I followed the instructions word for word twice. Any idea what it could be? Just to make sure, this should delete the whole row A and B if the keyword is in B, right?

Thanks

 

[Michael M]

OK firstly, are the keywords "trumpet" and "horn"
Secondly, do you want to delete the row or the column.
Thirdly, when you try to run the macro, is the sheet in question the active sheet ?

 

[fernandosaez]

Hi! Thanks so much for you time with this. Yes it is the only sheet in the work book.

For a somewhat real example. Under column A, I have the company info like:

Move Forward SpA, Portugal, Santiago de Chile

On column B I have the data such as (this can be a whole paragraph of info):

Trumpet-house-car-window-horn

*The idea is that if the word is in column B, the the info in A and B deletes (The row).
*The actual word is not trumpet but I'm not allowed to say exactly what we sell. During my test I put Horn in column B to see if it would be removed. It didn't.
*Some times I have to find upto 4 or 5 products and remove the company related to them, so adding and removing kew

 

[Michael M]

Ok, I notice Horn is with a capital H.
So, if this is the case try this and make sure you copy ALL the code as one block

Option Compare Text
Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 2 Step -1
    If InStr(Range("B" & r), "trumpet") Or InStr(Range("B" & r), "horn") Then
        Rows(r).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub

 

[fernandosaez]

AMAZING!!

Can I add more OR statements like this:

If InStr(Range("B" & r), "trumpet") Or InStr(Range("B" & r), "horn") Or InStr(Range("B" & r), "car") Then

Thank you so much!!!