Delete lines with a string in cells

[slick2000]

I am looking for a simple VBA function that will delete a line if there is a certain string inside a cell.
The string does NOT have to be exact.
Example: I want to delete all lines that have the word "string1" inside them, the strings are always in column C
cell C2 contains : "bla bla bla string1 bla bla"
cell C87 contains :" bla string1 bla bla bla bla".
cell C1234 : "string1 bla bla bla

etc

thank you very much !

Nicola

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub slick()
   With ActiveSheet
      .Range("A1:C1").AutoFilter 3, "*string 1*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I know your sample is just made-up, but what should happen to row 5 if C5 happened to contain "Longstring123"?
I'm asking because in your sample "string1" is always surrounded by spaces or is at the start/end of the cell text

 

[slick2000]

Thanks for your reply, I modified the generic word "string1" in "Scherm", an actual word, but I get an immediate error

I tried with and without the spaces

 

[Peter_SSs]

Could we see a small section at least of your worksheet with XL2BB?

Also note in any case that would not pick up a row if "Scherm" was the first or last word in the cell.

 

[Fluff]

In addition to Peter's comments, is your data in a table, or just a normal range?

 

[slick2000]

the A1 cell was empty, so the error, I had to write something in A1
I specified A1:C65000 and it works

many thanks

 

[Peter_SSs]

Good news.

Does it work fast enough? Deleting lots of disjoint rows can be relatively slow and if you do have a large number of rows like that there is a faster way.
If you are interested in that, post back but include more details about whether "Scherm" or whatever the word is could be the first/last word in a cell.

 

[slick2000]

yes, it looks fast enough to me, it's ok

 

[Peter_SSs]

All good then.