Delete Entire Rows based on multiple text values across multiple worksheets

[JohanGduToit]

Afternoon All,

I am attempting to delete entire rows across multiple worksheets based on text value criteria contained in Column B (on every worksheet). So as the code cycle through each worksheet, it should find rows where Column B values are either "*soh*" or "*IT NETT*" and delete those rows on every worksheet.

My code below looks for a specific worksheet named "Updated Allocation List" and if it exist it gets deleted. The top 4 rows are also deleted from the other worksheets.

The section between "Start Filter and Delete" and "End Filter and Delete" comments works; but only on the last worksheet; it does not delete the relevant rows from any of the other worksheets. I am also not sure how to specify the criteria to be both "*soh*" and "*IT NETT*".

Please assist? Thank you!!

Public Sub FormatMarkham01(sFile As String)

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

    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
                    
                    [B]'Start Filter and Delete
                    '.Application.AutoFilterMode = False
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*soh*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    'End Filter and Delete[/B]
            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

 

[JohanGduToit]

Hi Team,

I have managed to figure some of it out... by adding the ".Applicationn.Sheets(I).Select" statement all rows containing "*soh*" are being deleted across all worksheets.

However, I need assistance to remove the AutoFilter once the rows have been deleted; currently the filters remain afterwards and I need to see all rows (on all worksheets) once the procedure has completed. So my ".Application.AutoFilterMode = False" below does not work.

I also need to know how to specify another filter criteria in addition to "*soh*. The code must filter and delete on both "*soh*" and "*IT Nett*" values.

Any help greatly appreciated!!

'Start Filter and Delete
                   [CODE=vba] '.Application.AutoFilterMode = False
                    .Application.Sheets(I).Select
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*soh*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With

'End Filter and Delete[/CODE]

 

[JohanGduToit]

Correction... The code must filter and delete on both "*soh*" OR "*IT Nett*" values (so if Column B contain either of the two values) and all filters should be removed on completion.

 

[JohanGduToit]

I have managed to remove filters by adding .Selection.AutoFilter

 .Application.Sheets(I).Select
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*soh*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Selection.AutoFilter

Now I only need to figure out how to specify multiple filter values ("*soh*" and/or "*IT Nett*")

 

[JohanGduToit]

I have done a 'dirty' fix by adding a 2nd filter after the initial filter and delete: 1st filter on "*soh*" and delete those rows; reset filter; then do a 2nd filter on "*IT Nett*" and delete those rows; reset filter afterwards. Working code below; not sure if the two items that are filtered can be done in one 'with' nest.

'Start Filter and Delete
                    .Application.Sheets(I).Select
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*soh*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Application.Selection.AutoFilter
                    
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                        .AutoFilter 1, "*IT Nett*"
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Application.Selection.AutoFilter
                    'End Filter and Delete

 

[JohanGduToit]

Solved... filter based on multiple selection criteria

'Start Filter and Delete
                    .Application.Sheets(I).Select
                    With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
                       [B] .AutoFilter 1, "*soh*", xlOr, "*IT Nett*"[/B]
                        On Error Resume Next
                        .Offset(1).SpecialCells(12).EntireRow.Delete
                    End With
                    .Application.Selection.AutoFilter
 'End Filter and Delete