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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Cobalt()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("superseded", "inactive", "defunct")
   
   For i = 0 To UBound(Ary)
      Range("K:K").Replace Ary(i) & "*", True, xlWhole, , False, , False, False
   Next i
   Range("K:K").SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End Sub

 

[Cobolt78]

Thanks for your quick response but I got a Run-time error '1004' Delete Method of Range class failed in the yellow fonted line.
Might be a stupid question some of the repetitive words like *superseded* *inactive* have the * before and after the word but say in my below example "CO Fatigue" doesn't have an * before and after, would that make it fail?

Sub Cobalt()
Dim Ary As Variant
Dim i As Long

Ary = Array("superseded", "inactive", "CO Fatigue")

For i = 0 To UBound(Ary)
Range("K:K").Replace Ary(i) & "*", True, xlWhole, , False, , False, False
Next i
Range("K:K").SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End Sub

 

[Fluff]

In that case try

 Range("K:K").Replace "*" & Ary(i) & "*", True, xlWhole, , False, , False, False

Also is the sheet protected?

 

[Cobolt78]

Thanks, no the sheet isn't protected, I still get the same error in the same place.

Here is the current macro I use, I have removed a lot of repeating text it may contain sensitive data.
I am just finishing a 13hr nightshift so I'm pretty stuffed hahaha. I may not reply today but will test it once I wake up.
Thanks heaps for your help

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    With Sheets("Compliance")

        'select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the K column in this example
            With .Cells(Lrow, "K")

                If Not IsError(.Value) Then

                                     
                   
           If Not IsError(Application.Match(.Value, _
           Array("*Superseded* Driver Program (Inc LV Tyre Change Auth)", "*SUPERSEDED* Cable Management Training", _
           , 0)) Then .EntireRow.Delete
                   
                   
                
                   
                   

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

 

[Fluff]

Is your data in a structured table, or just a normal range?

 

[Cobolt78]

Is your data in a structured table, or just a normal range?

Sorry for the delayed response and thanks for your help. I believe the data is in a structured table.

 

[Fluff]

In that case what is the name of the table & what is the "name" of column K?

 

[Cobolt78]

In that case what is the name of the table & what is the "name" of column K?

As I am new to this I didn't want to waste your time so I had to do some learning before replying. the name of the table is "Table_RawReport_PSC_NEW"
and the K column is named "Entity Title"
Not sure if it will help but i could attach a pic of the sheet?
Thanks

 

[Fluff]

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