Using Countif with wildcard to delete rows

[DavidSCowan]

Hi there,
I am a VBA novice and I would greatly appreciate some help.
I am using Countif in a relatively simple macro to identify if a cell contains a particular string. The idea is to delete the row if the string is present.
So for example the two lines of code would read:
If Application.WorksheetFunction.CountIf(Cells(j, 1), "*Aerosol*") = 1 Then
Cells(j, 1).EntireRow.Delete
i.e. delete the row j if Cells(j,1) contains the word “Aerosol”
This works but I would like to replace Aerosol with Cells(i,2) so that I can cycle through a column of words in Column 2 and delete the row if the relevant words are present.
But what do I replace “*Aerosol*” with?
I have tried "*Cells(i,2)*" and "*Cells(i,2).value*" but with both when I F8 through the macro the looping works but rows that should be deleted aren’t i.e. the result is that no rows are deleted
When I replace "*Aerosol*" with *Cells(i,2).value* (i.e. no “ “) this produces an error (Compile Error Expected expression)
What should I replace "*Aerosol*" with?
Thank you in advance

 

[Fowmy]

Try this:

"*" & Cells(i,2) & "*"

 

[DavidSCowan]

Brilliant Fowmy - thank you very much - it worked a treat!

David