Remove everything but multiple text OR remove row if cells contact certain text value

[godzilla185]

Hi everyone, if anyone can help me on the below.

I am trying to get a macro that will delete rows if certain cells contact certain text, or even better, delete all rows except ones containing certain text.

The below code works great only problem is it only works on 1 variable "name1", but I need it to remove multiple names (10+)... If there is a simple way for me to elaborate on this code it would be easiest for me, but I tried adding OR/AND to the "name1" and it doesn't work.


Sub pivot()

' Filter_out_text Macro
'
Application.ScreenUpdating = False
Dim i As Long
LR = Range("O" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("O" & i) = "name1" Then
Rows(i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please_Array()
'Modified  9/27/2021  1:15:54 PM  EDT
Dim Del As Variant
Del = Array("Mary", "Mark", "Alex", "John") ' Modify search values
Dim lastrow As Long
Dim c As Long
Dim counter As Long
c = "15" ' Column Number Modify this to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row

With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, Del, Operator:=xlFilterValues
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.