Delete rows from list where field contains specific value!
Browse: Home
/ 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