Delete rows from list where field contains specific value!


Sub Delete_data_Macro()

    Dim strName As String
    'InputBox
    strName = MsgBox("You are about to DELETE the existing deal from the list, are you sure?", vbYesNo)
    'Exit sub if Cancel button used or no text entered
    If strName = vbNo Then Exit Sub

    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    Key1 = Range("A23").Value
    Key2 = Range("A24").Value
    Key3 = Range("A25").Value
    Key4 = Range("A26").Value
    Key5 = Range("A27").Value
    Key6 = Range("A28").Value
    Key7 = Range("A29").Value
    Key8 = Range("A30").Value
  

    'Fill in the values that you want to delete
    myArr = Array(Key1, Key2, Key3, Key4, Key5, Key6, Key7, Key8)

    For I = LBound(myArr) To UBound(myArr)

        'Sheet with the data, you can also use Sheets("MySheet")
        With Sheets("Rates Deal_list 2012")

            'Firstly, remove the AutoFilter
            .AutoFilterMode = False

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

            Set rng = Nothing
            With .AutoFilter.Range
                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 I

    Sheets("Mutation form").Select
    Range("A1").Select

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

End Sub