Filter on Multiple Values and Delete relevant Rows

[JohanGduToit]

Afternoon Experts,

I have some VBA code that filters on 4x different wildcard values and then delete all rows containing the filtered results. For some reason, the 1st two items are filtered and relevant rows deleted; but the 3rd and 4th values are being ignored and rows containing those two wildcard items are not being deleted.

The 1st filter statement (on *Markham* xlor *Dummy* works fine; but both *Closed* and *Do Not Use* are 'being ignored'. Is this got something to do with 'xlor' maybe? Is there another way to specify the 4x values that must be filtered and deleted?

Please see code starting with 'Start Filter and Delete of unwanted Store Descriptions below.

Public Sub FormatMarkham01(sFile As String)

'Delete Worksheet <Updated Allocation List> if sheet exist, Delete Rows 1 to 4 on all remaining worksheets and Delete all Rows containing specified text on all remaining worksheets

    Dim xlApp As Object
    Dim xlSheet As Object
        
    Dim I As Long
           
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Markham Sales File (Stage 1)... Please wait.")

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

    With xlApp
        .Application.ScreenUpdating = False
        .Application.DisplayAlerts = False
        For I = .Application.Sheets.Count To 1 Step -1
            If .Application.Sheets(I).Name = "Updated Allocation List" Then
                .Application.Sheets(I).Delete
                Else
                    .Application.Sheets(I).Rows(1).Resize(4).Delete
                    
                    'Start Filter and Delete
                    .Application.Sheets(I).Select
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*soh*", xlOr, "*IT Nett*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Application.Selection.AutoFilter
                    'End Filter and Delete
                    
                    'Start Copying Even Row Cells in Column A down to Uneven (Next) Cells in Column A
                    With .Application.Range("A2:A" & Range("B" & .Application.Rows.Count).End(xlUp).Row)
                        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
                        .Value = .Value
                    End With
                    'End Copying Even Row Cells down
                    
                    'Start Filter and Delete of unwanted Store Descriptions
                    With .Application.Range("A1", .Application.Range("A" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*Markham*", xlOr, "*Dummy*", xlOr, "*Closed*", xlOr, "*Do Not Use*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Application.Selection.AutoFilter
                    'End Filter and Delete of unwanted Store Descriptions
                                        
            End If
        Next
        .Application.ScreenUpdating = True
                      
        .Application.Sheets(1).Select
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
   End With
   
   vStatusBar = SysCmd(acSysCmdClearStatus)

   Set xlSheet = Nothing
   Set xlApp = Nothing
    
End Sub

 

[Fluff]

When using partial match you can only filter on 2 criteria.
As you have 4 criteria, the simplest solution would be to filter for the 1st 2 & delete the rows, then repeat for the 2nd 2

 

[JohanGduToit]

Thank you Fluff, worked like a charm! I was not aware of the limitation when using "xlor'.

Just as a matter of interest, do you know of any other way of applying a filter and deletion of rows by specifying more than 2 "partially matched" items... I'm thinking in terms of maybe using an array containing all the relevant items in one code-line without having to "split" code into "pairs" of two items at a time? Just asking...

Appreciate your feedback and assistance.

 

[Fluff]

There are various different ways, you could (amongst other options)
1) Put a formula in an unused column that returns true or false depending on whether that row should be deleted & then filter on that.
2) use an Advanced filter rather than autofilter
3) Loop through the column & store the actual values for the rows that need to be deleted in an array & then filter on the array.

 

[JohanGduToit]

Thank you Fluff! Applied your initial suggestion, and since it is working I will leave it at that.

Regards

 

[Fluff]

Glad to help & thanks for the feedback.