Excel- Delete rows if a cell in a particular column contains values found in another column

Question

I want to delete all the rows if the value in Vendor column is found in another column. (lookupVendor Col in another sheet)

MainSheet

LookupColumn

VendorCol
google 
microsoft
apple
vendorA
VendorB

Here is what I have but it only delete for one value. How can I make it use the entire LookupColumn and detete any rows that colc value is found in lookupColumn. My simple VBA code handles one value at a time.(which is not efficient)

Sub Find_Vendor()
    Dim rng As Range
    Dim what As String
    what = "Microsoft"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

Mainsheet: Contains 600,000 rows LookupColumn contains 400 entries. The existing code is super slow for the volume of data I have.

DisplayName DisplayName · Accepted Answer · 2018-04-25T17:55:08

use AutoFilter():

Option Explicit

Public Sub Find_Vendor()
    Dim filters As Variant
    With Sheets("LookupColumnSheet") ' change "LookupColumnSheet" to your actual sheet with lookup values name
        filters = Application.Transpose(.Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Value) ' collect "LookupColumnSheet" sheet column A values from row 1 down to last not empty row
    End With

    With Sheets("MainSheet") 'reference "MainSheet" sheet (change "MainSheet" to your actual "MainSheet" sheet name
        With .Range("C1", .Cells(.Rows.Count, 3).End(xlUp)) ' reference referenced sheet column C cells from row 1 down to last not empty one
            .AutoFilter Field:=1, Criteria1:=filters, Operator:=xlFilterValues ' filter referenced range with values from "LookupColumnSheet" sheet column A
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' if any filtered cells other than header then delete their entire row
        End With
        .AutoFilterMode = False ' remove filters
    End With
End Sub

Excel- Delete rows if a cell in a particular column contains values found in another column

1 Answers