Delete words in a column based off a list in another column

[Eric G]

How do you delete specific words in one column that's based off a list of words in another column?

For example, let's say in column A I have:

A1: Carrots are orange.
A2: Green peas go great with garlic butter.
A3: Watermelons are red on the inside and green on the outside.

And in column C are the specific words I want to eliminate in column A:

C1: orange
C2: green
C3: red

What's the VBA code to do this?

 

[My Aswer Is This]

Try this:

Sub FindReplaceAll()
'Modified  10/29/2020  9:10:51 AM  EDT
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastrow
          Cells(i, 1).Replace What:=Cells(i, 3).Value, Replacement:=""
    Next
End Sub

 

[Eric G]

Try this:

Sub FindReplaceAll()
'Modified  10/29/2020  9:10:51 AM  EDT
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
   
    For i = 1 To lastrow
          Cells(i, 1).Replace What:=Cells(i, 3).Value, Replacement:=""
    Next
End Sub

Terrific. Works as advertised. Thanks!

 

[My Aswer Is This]

Terrific. Works as advertised. Thanks!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Eric G]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Looks like I spoke too soon. I must apologize as I could have written my question better, but would it be possible to tweak the code just a little bit?

As it stands now, the code will compare the text in A1 with the word in C1 to determine whether to do a find and replace. And then the code works down these two columns for each row. However, while the code does remove the word "green" in A2 it misses the same word in A3 and only removes the word "red."

Is it possible to rewrite the code so it scours all of column A for any of the words listed in column C? And then, of course, deletes those words as it does in your original code.

 

[Fluff]

Could you have a situation where one of the words in col C is contained in a word in col A (ie C4= melons)?
In that scenario should Watermelons become Water?

 

[Fluff]

If partial matches aren't a problem, try

Sub EricG()
   For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
      Range("A:A").Replace Cl.Value, "", xlPart, , False, , False, False
   Next Cl
End Sub

 

[Eric G]

If partial matches aren't a problem, try

Sub EricG()
   For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
      Range("A:A").Replace Cl.Value, "", xlPart, , False, , False, False
   Next Cl
End Sub

I think this does the trick. Thanks, Fluff.

And, yes, I do have situations where I'd only want to delete "melons" and the code inadvertently targets "Watermelons." However, I can compensate for this by entering a single space before and after the word melons and adding "MatchCase:=True" to the code for the more obnoxious, hard to get at weeds. Which is what I normally do when I use CTRL-H, just using find and replace one word at a time is quite tedious, hence the desire for some VBA code to save the day.

Again, thanks to the both of you.

 

[Fluff]

Glad we could help & thanks for the feedback.