VBA to delete all rows in a sheet except rows with specific cells in specific column

[Dustinkli]

I have a spreadsheet and I need to delete all rows that do NOT have specified values in a column. Essentially I would like to delete all rows that do not contain specific words from a preserve list. The cells in the column will have full sentences in them so I want it to just isolate if the specified word exists anywhere in the cell and NOT delete that row.

I have two working ways to do something similar to this:

Method 1:

Sub deletexptlst()
Dim List As Variant
    Dim LR As Long
    Dim r As Long
    List = Array("Value 1", "Value 2", "Value 3", "Value 4")
    LR = Range("E" & Rows.Count).End(xlUp).Row
    For r = LR To 2 Step -1
        If IsError(Application.Match(Range("E" & r).Value, List, False)) Then
            Rows(r).Delete
        End If
    Next r

End Sub

Method 1 works by making an array of the values I want to preserve, but it does not seem to be able (or I can't figure out how) to make it wildcard based so that the value can exist anywhere in the cell.


Method 2:

Sub delallexcept()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Not Range("E" & i).Value Like "*Value*" Then Rows(i).Delete
Next i
 ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Method 2 works with the wildcard "*" but I can't figure out how to add additional values to not be deleted.

 

[Joe4]

Try this:

Sub MyDeleteList()

    Dim List As Variant
    Dim LR As Long
    Dim r As Long
    Dim i As Long
    Dim keep As Boolean
    
    Application.ScreenUpdating = False
    
'   Store values to look for
    List = Array("Value 1", "Value 2", "Value 3", "Value 4")
    
'   Find last row with data in column E
    LR = Range("E" & Rows.Count).End(xlUp).Row
    
'   Loop through all rows, from bottom to top
    For r = LR To 2 Step -1
'       Set keep variable to false to start
        keep = False
'       Loop through all values in array
        For i = LBound(List) To UBound(List)
'           See if each value found in row
            If InStr(Range("E" & r).Value, List(i)) > 0 Then
'               If found, set keep to true and exit loop
                keep = True
                Exit For
            End If
        Next i
'       Check to see if we should delete row
        If keep = False Then Rows(r).Delete
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[Dustinkli]

That looks to work! Thank you very much!

How did you get it so the array list would allow for the variable anywhere in the cell?

 

[Joe4]

How did you get it so the array list would allow for the variable anywhere in the cell?

The key is the InStr function, which is a function that asks where in the string does another string exist. If it doesn't, it will return 0.
Take a look at this here to see details on how it works and examples: MS Excel: How to use the INSTR Function (VBA)