CODEWITHSUNDEEP
excelvbamacosmultiple-columns
Solomon Closson
Solomon Closson

Reputation: 6217

Remove entire row based on match?

Need to remove multiple rows within a CSV file in Excel based on a match in column L on sheet1 with column A on sheet2. The content is email addresses. How can I achieve this?

So, if sheet2 column A has an email address that matches any of the email addresses in sheet1 - Column L, than it should remove the entire row from sheet1 where that email address is located.

Sheet1 below: Sheet1

Sheet2 below: Sheet2

@mehow, here's the image I get when I run your code as a module: Error with mehow's code

Upvotes: 0

Views: 685

Answers (1)

user2140261
user2140261

Reputation: 8003

This will work, very fast for what you are looking to do as it doesn't involve ANY loops. 

Sub DeleteDuplicates()

Dim StartingScreenUpdateValue As Boolean
Dim StartingEventsValue As Boolean
Dim StartingCalculations As XlCalculation

With Application
    StartingScreenUpdateValue = .ScreenUpdating
    StartingEventsValue = .EnableEvents
    StartingCalculations = .Calculation
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Dim varTestValues As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

With sh2
    varTestValues = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With

sh1.Range("L1", sh1.Range("L" & sh1.Rows.Count).End(xlUp)) _
    .AutoFilter Field:=12, Criteria1:=Application.Transpose(varTestValues), Operator:=xlFilterValues

sh1.Range("L2", sh1.Range("L" & sh1.Rows.Count).End(xlUp)) _
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete

sh1.AutoFilterMode = False

With Application
    .ScreenUpdating = StartingScreenUpdateValue
    .EnableEvents = StartingEventsValue
    .Calculation = StartingCalculations
End With

End Sub

NOTE: This code runs assuming your data has headers if it does not please advise.

REMEMBER Always run any code on a copy of your data and not your actual data until you are confident that it is working 100%.

Upvotes: 1

Related Questions