VBA loop through multi-d array and keep values that meet criteria in one column

Question

I'd like to take a set of data on a sheet and remove any rows that contain specific text in one of the columns.

Initially, I thought I could just use autofilter but that has a limitation of two criteria for excluding. So my next thought was to place the data in an array and then loop through each row on that column and if it does not contain these phrases then place it in another array that i will use to paste into the sheet.

Here is the relevant code I've put together so far.

If (leaderShtArr(i) = vpPopulationSht) Then
    leaderShtLC = leaderShtArr(i).Cells(1, Columns.Count).End(xlToLeft).Column
    leaderShtLR = eaDBWorkersSht.Cells.Find(what:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    'Reorder clicked campaign names to Calendar Order
    Dim initialArr, filteredArr As Variant
     
    initialArr = vzReporterDataSht.Range("A2:" & ColLett(leaderShtLC) & newLR).Value2
    
    For x = 1 To UBound(initialArr)
        If (initialArr(x, jobTitleCurrShtCN) <> "*EVP*" AND initialArr(x, jobTitleCurrShtCN) <> "*SVP*" AND initialArr(x, jobTitleCurrShtCN) <> "*AVP*") Then
            'WHAT GOES HERE?
        End If
    Next x
    'Paste data back into the range.
    leaderShtArr(i).Cells.Clear
    leaderShtArr(i).Range(ColLett(campaignsReportedCN) & "2").Resize(UBound(filteredArr), 1).Value2 = filteredArr
End If

Here are my questions:

• Am I approaching this correctly for maximum speed and efficiency? If not what should I be looking to do?
• Is the If statement correct for finding cells within that column that "does not contain" these phrases?

Edit: This question was identified as similar to this one. It's similar because that was the approach I mentioned I tried but was not able to because of the limitation with "does not contain" criteria. I really would like to find a way to do it via arrays. Most of my time nowadays is spent on google sheets and loading to an array, doing work to the array and then pasting is a preferred method. I'm really interested how I can do that in VBA. If it's not acceptable or advisable, I undersand.

Answer 1

One way is to reuse the initial array but 'shuffle' the rows up into any deleted ones. For example

Sub demo()
    
    Dim arwords
    arwords = Array("EVP", "SVP", "AVP")
    
    'create regex engine
    Dim Regex As Object
    Set Regex = CreateObject("vbscript.regexp")
    With Regex
      .Global = False
      .MultiLine = False
      .IgnoreCase = True
      .Pattern = Join(arwords, "|")
    End With

    Dim initialArr, v
    Dim n As Long, x As Long, y As Long, z As Long
    
    initialArr = Sheet1.Range("A1:D20").Value2
    
    For x = 1 To UBound(initialArr)
        v = initialArr(x, 1)
        If Regex.test(v) Then
            ' dont copy
            n = n + 1
        Else
            y = y + 1
            For z = 1 To UBound(initialArr, 2)
               initialArr(y, z) = initialArr(x, z)
            Next
        End If
    Next x
    
    If y > 0 Then
        Sheet2.Range("A1").Resize(y, UBound(initialArr, 2)) = initialArr
    End If
    
    MsgBox n & " rows removed", vbInformation

End Sub