Delete row if it contains a certain word or phrase - and use array

[1819]

This code (from the VBA Recorder) works well to find rows containing certain words and delete the whole row. (The rows in this instance only actually have column A filled, but I would the flexibility to do it across more columns).

However, I'd like to improve this by:

1) asking how the search terms could be combined into an array, so only one block of code would be necessary

2) asking whether there is a quicker method of doing the same thing.

Many thanks.



With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*award*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*thanks*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With


With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*job*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*join us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*joining us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

[Paul_Hossler]

One way.

Not tested




Option Explicit

Sub DeleteSomeRows()
Dim aWords As Variant
Dim iWord As Long

aWords = Array("award", "thanks", "job", "join us")

Application.ScreenUpdating = False
For iWord = LBound(aWords) To UBound(aWords)
Call DeleteRow(1, CStr(aWords(iWord)))
Next
Application.ScreenUpdating = True


End Sub

Private Sub DeleteRow(iColNum As Long, sWord As String)
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & .Rows.Count).End(xlUp))
.AutoFilter iColNum, "=*" & sWord & "*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
On Error GoTo 0
End With
.AutoFilterMode = False
End With
End Sub

[1819]

Another excellent response. Works well. Thanks Paul.