Hello,
I'm not sure which is the best way to do this. I have 2 sheets, 1 has a list, and the other has a 'remove list.'
I want to remove all Rows that contain anything on the remove list in Column A.
I was thinking about trying to do this using a vlookup and deleting the results that did not return as #N/A. But I think it would be easier just to check against the remove list (not use a vlookup).
This is the code I'm using but nothing happens when i run it (and no debug error)
My remove list is on Sheet4 ranges A2:A108
I'm not sure which is the best way to do this. I have 2 sheets, 1 has a list, and the other has a 'remove list.'
I want to remove all Rows that contain anything on the remove list in Column A.
I was thinking about trying to do this using a vlookup and deleting the results that did not return as #N/A. But I think it would be easier just to check against the remove list (not use a vlookup).
This is the code I'm using but nothing happens when i run it (and no debug error)
My remove list is on Sheet4 ranges A2:A108
Code:
Sub Example1()
Dim rngFound As Range, rngToDelete As Range
Dim strFirstAddress As String
Dim varList As Variant
Dim lngCounter As Long
Application.ScreenUpdating = False
varList = VBA.Array("Sheet4!A2:A108")
For lngCounter = LBound(varList) To UBound(varList)
With ActiveSheet.Range("A:A")
Set rngFound = .Find( _
What:=varList(lngCounter), _
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