Filter on Multiple Values and Delete relevant Rows

JohanGduToit

Board Regular
Joined
Nov 12, 2021
Messages
89
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
Thank you Fluff! Applied your initial suggestion, and since it is working I will leave it at that.

Regards
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top