VBA - Deleting matching data and its rows over multiple sheets based on a range of data

Question

I am trying to create a excel macro (with the help of google) that searches a set of data, using that as a criteria then search through all of the sheets in my workbook and deleting any rows with matching values in it.

the coding in this sheet only selects just one specific sheet, how could I modify it to have all of the worksheets (except the criteria sheet where I've posted my data) being subjected to the criteria ?

In addition, it seems if the matching value is in cell A1, the row will not be deleted. Have no idea why is this so.

I've tried using a counter or next ws, but couldn't figured out whats the problem. Greatly appreciate any help!

Sub Delete_with_Autofilter_More_Criteria()

Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long


With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

'setting criteria
With Sheets("Sheet1")        
      Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

'Loop through the cells in the Criteria range
For Each cell In CriteriaRng

    With Sheets("export3")

        'Firstly, remove the AutoFilter
        .AutoFilterMode = False

        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value

        With .AutoFilter.Range
            Set rng = Nothing
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete                               
        End With

        'Remove the AutoFilter
        .AutoFilterMode = False
    End With

Next cell

With Application
    .ScreenUpdating = True
    .Calculation = calcmode
End With

End Sub

you want to add a loop of all sheets instead of only searching through Sheets("export3") ? — Shai Rado
I tried adding a loop by using a counter... kept giving me some errors. Sorry I'm unable to paste those amended codes.. am on phone now — Clarence

Shai Rado Shai Rado · Accepted Answer · 2016-08-17T05:10:31

The modified code below will help you to loop through all the Sheets in ThisWorkbook (besides "Sheet1" where you have your CriteriaRng Set).

Sub Delete_with_Autofilter_More_Criteria()

Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long
Dim sht As Worksheet


With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

'setting criteria
With Sheets("Sheet1")
      Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

' loop through all sheets in this workbook
For Each sht In ThisWorkbook.Sheets

    ' don't run this comparison for the original sheet1
    If sht.Name <> "Sheet1" Then

        'Loop through the cells in the Criteria range
        For Each cell In CriteriaRng

            With sht
                'Firstly, remove the AutoFilter
                .AutoFilterMode = False

                'Apply the filter
                .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value

                With .AutoFilter.Range
                    Set rng = Nothing
                    On Error Resume Next
                    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                              .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                    If Not rng Is Nothing Then rng.EntireRow.Delete
                End With

                'Remove the AutoFilter
                .AutoFilterMode = False
            End With

        Next cell
    End If

Next sht

With Application
    .ScreenUpdating = True
    .Calculation = calcmode
End With

End Sub

VBA - Deleting matching data and its rows over multiple sheets based on a range of data

1 Answers