Auto Filter Array only Filtering by Last Criteria in Array

Question

I am trying to sort a table by deleting rows that have their cell in column 9 NOT beginning with S, X, or P. Below is the code that I have that filters for the rows that do not meet my criteria, and then deletes them, and then shows the remaining values.

Range("I:I").NumberFormat = "@"

    lo.Range.AutoFilter Field:=9, Criteria1:=Array("<>S*", "<>X*", "<>P*"), Operator:=xlOr
   
    Application.DisplayAlerts = False
     lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    lo.AutoFilter.ShowAllData

Currently, regardless or order, only rows that contain the last criteria in the array are kept.

Answer 1

Delete Multi-Criteria Rows of an Excel Table

• You cannot have more than two criteria (elements) with wild characters.
• As a workaround, this solution adds a new column and writes a formula to it. The formula returns a boolean indicating whether a string starts with the chars from the list. Then it filters the new column by False and deletes these filtered tables' (not worksheet's) rows. Finally, it deletes the new column.
• The data to the right (one empty column is assumed) stays intact, it is not shifted in any way hence the inserting and deleting of a worksheet column instead of using .ListColumns.Add.
• Adjust the values in the constants section.

Option Explicit

Sub DeleteMultiCriteriaRows()
    
    Const wsName As String = "Sheet1"
    Const tblName As String = "Table1"
    Const NotFirstCharList As String = "s,x,p"
    Const CritCol As Long = 9
    
    ' Extract chars for the formula.
    Dim Nfc() As String: Nfc = Split(NotFirstCharList, ",")
    Dim NotFirstChar As String: NotFirstChar = "{"
    Dim n As Long
    For n = 0 To UBound(Nfc)
        NotFirstChar = NotFirstChar & """" & Nfc(n) & ""","
    Next n
    NotFirstChar = Left(NotFirstChar, Len(NotFirstChar) - 1) & "}"
    Erase Nfc
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
    
    Application.ScreenUpdating = False
    
    With tbl
        
        If Not .ShowAutoFilter Then .ShowAutoFilter = True
        If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData ' remove filter
        
        .ListColumns(CritCol).DataBodyRange.NumberFormat = "@" ' ?
        
        Dim nFormula As String
        nFormula = "=ISNUMBER(MATCH(LEFT(" & .Name & "[@" _
            & .ListColumns(CritCol).Name & "],1)," & NotFirstChar & ",0))"
        
        Dim LastCol As Long: LastCol = .ListColumns.Count
        With .ListColumns(1) ' write formulas to newly inserted column
            .Range.Offset(, LastCol).EntireColumn.Insert
            .DataBodyRange.Offset(, LastCol).Formula = nFormula
        End With
        
        LastCol = LastCol + 1 ' think new column
        .Range.AutoFilter LastCol, False ' think Not(FirstChar)
        
        Dim vrg As Range ' Visible Range
        On Error Resume Next ' prevent 'No cells found...' error
            Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        .AutoFilter.ShowAllData  ' remove filter
        
        If Not vrg Is Nothing Then ' delete visible rows
            vrg.Delete Shift:=xlShiftUp
        End If
        
        .ListColumns(LastCol).Range.EntireColumn.Delete ' delete new column
    
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Answer 2

This code will delete any rows that have a value in the 9th column of the first table on the first sheet in a workbook that doesn't start with one of the letters in arrBeginsWith.

There are other ways to do achieve what you want, for example adding a helper column that identifies the rows to delete with a formula and then filtering on that column.

Option Explicit

Sub KeepRowsStartingWith()
Dim tbl As ListObject
Dim rngDelete As Range
Dim arrBeginsWith As Variant
Dim arrData As Variant
Dim idxRow As Long
Dim StartRow As Long
Dim Res As Variant

    Set tbl = Sheets(1).ListObjects(1)
    
    With tbl.ListColumns(9).DataBodyRange
        StartRow = .Cells(1, 1).Row
        arrData = .Value
    End With
    
    ReDim arrDeleteRows(1 To UBound(arrData, 1))
    
    arrBeginsWith = Array("S", "X", "P")
    
    For idxRow = 1 To UBound(arrData, 1)
    
        Res = Application.Match(Left(arrData(idxRow, 1), 1), arrBeginsWith, 0)
        
        If IsError(Res) Then
            If rngDelete Is Nothing Then
                Set rngDelete = Intersect(tbl.DataBodyRange, Sheets(1).Rows(idxRow + StartRow - 1))
            Else
                Set rngDelete = Union(rngDelete, Intersect(tbl.DataBodyRange, Sheets(1).Rows(idxRow + StartRow - 1)))
            End If
        End If
        
    Next idxRow

    rngDelete.Delete xlShiftUp
    
End Sub

Answer 3

I ended up creating a new column in my table with an if statement to identify if a cell began with a letter or number. Then I filtered for the rows that had a number, deleted those rows, and then showed the remaining rows. I then deleted the helper column as to not have to deal with it later.

ThisWorkbook.Worksheets("Aluminum Futures").Columns("T:T").Select
Selection.Insert Shift:=xlToRight
Range("T1") = "Letter/Number"


    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISERR(LEFT(RC[-11],1)*1),""letter"",""number"")"
    Range("T2").Select
    Selection.AutoFill Destination:=Range("PF[Letter/Number]")
    Range("PF[Letter/Number]").Select
    
    
    lo.Range.AutoFilter Field:=20, Criteria1:="number"
    
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    lo.AutoFilter.ShowAllData
    
    Columns("T:T").Delete