VBA- Delete Rows if Column A contains value from list

[Vexorg]

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

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

 

[Vexorg]

I actually figured this out using a vlookup and autofilter combination, but if anyone knows how to get the above to work i would appreciate that as well.

 

[Comfy]

Change

varList = VBA.Array("Sheet4!A2:A108")

to

varList = Range("Sheet4!A2:A108").Value

and

Set rngFound = .Find( _
                                What:=varList(lngCounter), _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True _
                                    )

to

Set rngFound = .Find( _
                                What:=varList(lngCounter, 1), _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True _
                                    )

 

[Vexorg]

Thanks Comfy that worked perfectly!