VBA - Deleting rows if certain cell values are not present

[joaoc]

Hello everyone.
I'm doing a project where I have to make selection within a lot of information (more than 20,000 rows of data). You can see in the image there are some columns. What I need to do is to DELETE every row where the F column or the H column has a different value from the following:

Arganil
Cantanhede
Coimbra
Condeixa-a-Nova
Figueira da Foz
Góis
Lousã
Mealhada
Mira
Miranda do Corvo
Montemor-o-Velho
Mortágua
Oliveira do Hospital
Pampilhosa da Serra
Penacova
Penela
Soure
Tábua
Vila Nova de Poiares

So, for example, if there was a row, where the F or the H column would say "Anadia" (this word is NOT on the list above), that row would be deleted.
I'm having a bit of trouble coming up with a woking macro, specially because what you have to delete is what is not present on the list. Because I don't know all the names that are going to appear, so the only way I can do the macro is telling what NOT to delete. I hope I made my self understand.
Thank you so much for your time.

 

[kevin9999]

Where do you have the list of names above - are they on another sheet? Also, what row are the headers of your table on?

 

[joaoc]

Where do you have the list of names above - are they on another sheet? Also, what row are the headers of your table on?

The names above are present throughout the rows. You can see the first rows in the image include "Arganil" and "Miranda do Corvo", which are on the list I mentioned above, so these rows should NOT be deleted. The headers is on row 6, but I can I change that to the first row if needed.

 

[kevin9999]

Thank you for your response. I understand that the names are in columns F & H, but do you have them listed anywhere else in your workbook - or does the list never change?

 

[joaoc]

Thank you for your response. I understand that the names are in columns F & H, but do you have them listed anywhere else in your workbook - or does the list never change?

The list is fixed with thousands of rows, with various names (the names repeat often), and I just need to eliminate the rows that include any name other than the 19 names listed above. By the way, thank you for your time

 

[Akuini]

@joaoc
Try this:

Sub joaoc_1()
Dim i As Long, n As Long
Dim va, vb, ary

ary = Split("Arganil|Cantanhede|Coimbra|Condeixa -a - Nova|Figueira da Foz|Góis|Lousã|Mealhada|Mira|Miranda do Corvo|Montemor -o - Velho|Mortágua|Oliveira do Hospital|Pampilhosa da Serra|Penacova|Penela|Soure|Tábua|Vila Nova de Poiares", "|")
n = Range("F" & Rows.Count).End(xlUp).Row
va = Range("F7:F" & n)
vb = Range("H7:H" & n)

For i = 1 To UBound(va, 1)
    If IsNumeric(Application.Match(va(i, 1), ary, 0)) Then
        If IsError(Application.Match(vb(i, 1), ary, 0)) Then
            vb(i, 1) = "FALSE"
        End If
    Else
        va(i, 1) = "FALSE"
    End If
Next

Range("F7:F" & n) = va
Range("H7:H" & n) = vb

On Error Resume Next
Range("F7:F" & n).SpecialCells(xlConstants, xlLogical).EntireRow.ClearContents
Range("H7:H" & n).SpecialCells(xlConstants, xlLogical).EntireRow.ClearContents
On Error GoTo 0

End Sub

where the F column or the H column

the operator is OR not AND, so:
if col F OR col H value isn't on the list then the entire row will be cleared but not deleted. So you need to sort the data to "remove" the empty rows.
The reason: deleting disjointed rows on large data could be slow, so it's faster just to delete the content then sort the data.

 

[kevin9999]

I’d like to offer the following alternative to the excellent response from @Akuini. My version does not hard-code the inclusion list within the sub routine, but instead uses a list you maintain on a sheet in your workbook. The reason for this is that you don’t know what the future will bring, and you may want to change the list at some point, which will be easier to do on a sheet rather than within the code itself.

The code assumes your table of values is in sheet 1, and that your ‘fixed’ list is on sheet2 – starting from cell A1 down. The code will remove all rows that include any name not on your list in either column F or H. In testing, it processed 50K rows of test data in slightly under 1 second.

Option Explicit
Sub joaoc2()
    Dim arr, arr2, Result
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Long, j As Long, lr As Long
    Set ws1 = Sheets("Sheet1")  '<< change to suit
    Set ws2 = Sheets("Sheet2")  '<< change to suit
    lr = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    arr = ws2.Range("A1:A" & lr).Value
    arr = Application.Transpose(Application.Index(arr, 0, 1))
    
    lr = ws1.Cells(Rows.Count, 2).End(xlUp).Row
    arr2 = ws1.Range("F7:H" & lr)
    ReDim Result(1 To UBound(arr2), 1 To 1)
    
    For i = LBound(arr2) To UBound(arr2)
        If IsItIn(arr, arr2(i, 1)) = True And IsItIn(arr, arr2(i, 3)) = True Then
            Result(i, 1) = 1
        End If
    Next i
    
    Application.ScreenUpdating = False
    ws1.Range("L7").Resize(UBound(Result)) = Result
    j = WorksheetFunction.Sum(ws1.Range("L:L")) + 7
    
    With ws1.Range("B7:L" & lr)
        .Sort Key1:=ws1.Range("L7"), order1:=xlDescending, Header:=xlNo
    End With
    
    ws1.Range("A" & j & ":A" & lr).EntireRow.Delete
    ws1.Range("L:L").ClearContents
    Application.ScreenUpdating = True
End Sub
Public Function IsItIn(myArray, myValue)
    IsItIn = False
    Dim i As Long
    For i = LBound(myArray) To UBound(myArray)
        If myArray(i) = myValue Then
            IsItIn = True
            Exit For
        End If
    Next
End Function

 

[joaoc]

Thank you so much to both of you @Akuini and @kevin9999 for your time and attention. I'm sorry for the delayed response, but I have been a bit ill in that last few days. I will test out both of your solutions. Thank you so much once again.