VBA delete row with multiple criteria using wildcards

[Cobolt78]

Hi, I am very new at excel and vba and have been looking for a macro that will search for partial matches using wildcards and delete rows in column k. I found a macro that works well with multiple criteria but the macro has to match the cell exactly.
Let me explain with an example, I have a sheet that I import data from a report. I have column k with 20,000 plus rows and a lot of cells in column k have useless information I wish to remove such as "*superseded* xyz abc" and *superseded* bla bla bla" and "*inactive* xyz abc" and "*inactive* bla bla bla" and "*defunct* xyz abc" and "*defunct* bla bla bla". The *superseded*, *inactive* and *defunct* have up to 20-30 different types of text after them so it makes it a tedious process to copy and paste them all into my current vba macro. Is there a way to just search and delete rows with the key words *superseded*, *inactive* and *defunct*? I would also like it to be dynamic so I can add more criteria later like a keyword "*remove*"
I don't need any other data in the rows with the key words so deleting the whole row is fine.
Hopefully I have explained myself properly.

Thanks in advance

 

[Cobolt78]

Ok, how about

Sub Cobalt()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array( "superseded", "inactive", "CO Fatigue" )
   With Sheets("Compliance").ListObjects("Table_RawReport_PSC_NEW").ListColumns("Entity Title")
      For i = 0 To UBound(Ary)
         .Range.Replace "*" & Ary(i) & "*", True, xlWhole, , False, , False, False
      Next i
      Intersect(.Parent.DataBodyRange, .Range.SpecialCells(xlConstants, xlLogical).EntireRow).Delete
   End With
End Sub

Thank you so much, this worked a treat. I greatly appreciate the time you diverted into helping me and I hope this helps someone else in the future.

 

[Cobolt78]

One more quick question, How would I hide the rows instead of deleting them if I wished to. I tried .Hidden = True instead of .Delete but obviously that didn't work.
Thanks

 

[Fluff]

To hide the rows use

      Next i
      .Range.SpecialCells(xlConstants, xlLogical).EntireRow.Hidden = True
   End With

but be aware that the code is changing the values in the cells.

 

[Cobolt78]

To hide the rows use

      Next i
      .Range.SpecialCells(xlConstants, xlLogical).EntireRow.Hidden = True
   End With

but be aware that the code is changing the values in the cells.

Thanks that worked however when I un-hide all the rows they have TRUE in the "Entity Title" k column instead of the original text. Is there a way to preserve the original text when I un-hide the rows?

Sorry to be a pain.

 

[Fluff]

Not with that code. You would need a totally different solution.

 

[Cobolt78]

Not with that code. You would need a totally different solution.

Ahh ok thank you anyway.
I'll leave you alone now hahaha.

 

[Cobolt78]

Not with that code. You would need a totally different solution.

Just had a thought, maybe a stupid way of doing it but do you think I could just add at the start of the code to copy K column to say a hidden "L" column and just change your code from "Entity Title" to what ever I call the L column that way it would just put "TRUE in L column and leave the real data alone in the K column?
Not elegant but would allow me to change the code to either delete or hide relatively easy if needed.
Just a thought.

 

[Fluff]

Yes you could do that, or you could put a formula in the hidden column that returns true or false & use the autofilter.

 

[Cobolt78]

Yes you could do that, or you could put a formula in the hidden column that returns true or false & use the autofilter.

Nice, thanks for another option.

 

[Fluff]

My pleasure.