Delete Rows based on values in a separate sheet

[caribou48]

Hello ,


I am a complete VBA rookie, but a pretty advanced excel user.
I am trying to work a macro that will delete rows in a sheet based on
exclusions I have defined on another sheet.

worksheet

Columns
A B C D E F G H I

With varying number of rows depending on the report I am running. but in the region of 500


exclusion Sheet contains

Column
A
with 405 rows of exclusions, which are specific words


I want the macros to look through the worksheet and delete any row that contains a word that appears on the exclusion list.

I am getting a 424 error. I am sure I have made a pigs ear of this , This is probably my first serious attempt to code VBA I have pieced the code together from other posts I have seen. and my very limited knowledge.
.
Any help is greatly appreciated.

CODE:

Sub CCSExclusions()

Dim rngFound As Range, rngToDelete As Range
Dim strFirstAddress As String
Dim varList As Variant
Dim lngCounter As Long

Application.ScreenUpdating = False

varList = Range("Exclusions!A1:A405").Value

For lngCounter = LBound(varList) To UBound(varList)

With ActiveSheet.Range("A:I")
Set rngFound = .Find( _
What:=varList(lngCounter, 1), _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True _
)


If Not rngFound Is Nothing Then
If rngToDelete Is Nothing Then
Set rngToDelete = rngFound
Else
Set rngToDelete = Application.Union(rngToDelete, rngFound)
End If

strFirstAddress = rngFound.Address
Set rngFound = .FindNext(After:=rngFound)

Do Until rngFound.Address = strFirstAddress
Set rngToDelete = Application.Union(rngToDelete, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
Next lngCounter

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Application.ScreenUpdating = True

End Sub


I am getting an error with

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
????