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]

What are the values your looking for?
You showed : "name1"
Is that really the value your looking for?
And then what?
You need to show us the values to look for.
Like tell me "Alpha" then "Bravo" then "Charlie"

 

[My Aswer Is This]

What are the values your looking for?
You showed : "name1"
Is that really the value your looking for?
And then what?
You need to show us the values to look for.
Like tell me "Alpha" then "Bravo" then "Charlie"

Or say look in sheet named "Master" Range("A1:A20") for the search for values.

 

[godzilla185]

What are the values your looking for?
You showed : "name1"
Is that really the value your looking for?
And then what?
You need to show us the values to look for.
Like tell me "Alpha" then "Bravo" then "Charlie"

Ahh ok, apologies I am new with VBA macros.

I need to insert a bunch of different names, such as "Mary", "Mark", "Alex", "John". I'd need to be able to change these names later on since I am using it for my work, but let's say we use those 4 for now. It needs to be exact text as well.

All I need is, whichever code is easier, either to delete all rows but those names, or delete rows containing those text names (I can just replace the names depending on which version).

 

[Fluff]

Hi & welcome to MrExcel.
Do you have a header row? if so what row is it?

 

[godzilla185]

I apologize i tried using the mini-excel but it doesnt seem to work.

The picture above is an example. I need to keep "Mary", "George" and "Andrew", and remove everything else. I need it to be in the main worksheet, which I usually rename as "data" if need be.

The code below allows me to use the macro in any sheet, and it allows me to remove 1 exact text name, but it won't let me select more. So was wondering if it can be expanded, or changed as you deem best I appreciate your time

Sub pivot()

' Filter_out_text Macro
'
Application.ScreenUpdating = False
Dim i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("C" & i) = "Mary" 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

 

[godzilla185]

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

This works like magic. Thank you very much exactly what i needed

 

[My Aswer Is This]

This works like magic. Thank you very much exactly what i needed

You can add more values to delete just keep adding them like I did.

 

[JEC]

One more option, which leaves the names you want to see

Sub jec()
 Application.ScreenUpdating = False
 With Range("O1", Cells(Rows.Count, "O").End(xlUp))
   .Value = Evaluate(Replace("if(match(%%,{""Mark"",""Mary"",""Alex"",""John""},0),%%)", "%%", .Address))
   .SpecialCells(2, xlErrors).EntireRow.Delete
 End With
End Sub