autofilter not equal to multiple criterias

[TheMadTree]

Good afternoon!

I am looking for a way to filter/delete everything out of a column besides 5 strings of text.
I tried looping through the 8000+ rows evaluating if the string of text isn't one I want to keep, but the execution of this code takes far too long:

For i = LastRow To 2 Step -1
If Cells(i, 1) <> "text1" And Cells(i, 1) <> "text2" And Cells(i, 1) <> "text3" And Cells(i, 1) <> "text4" And Cells(i, 1) <> "text5" Then Rows(i).Delete
Next

And I can't seem to auto filter everything out I don't want to keep as we're only entitled to two criteria.
(Filtering out all other values besides the 5 string of text I want to keep with and array is not an option as they're too many and can vary)

Does anyone have a possible solution and/or workaround?

Thank you,

Elias

 

[Marcelo Branco]

A possible workaround

Create a helper column with the formula
=ISERROR(MATCH(A2,{"text1";"text2";"text3";"text4";"text5"},0))
and filter the data using this column as criterion

Hope this helps

M.

 

[Akuini]

This is a vba solution:

Sub a939630a()
Dim va, i As Long, r As Range
Set r = Range("A2", Cells(Rows.count, "A").End(xlUp))
va = r.Value
For i = 1 To UBound(va, 1)
    If va(i, 1) <> "text1" And va(i, 1) <> "text2" And va(i, 1) <> "text3" And va(i, 1) <> "text4" And va(i, 1) <> "text5" Then
         va(i, 1) = ""
    End If
Next
r.ClearContents
Range("A2").Resize(UBound(va, 1), 1) = va
r.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub