0

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Solomon Closson
  • 6,111
  • 14
  • 73
  • 115

1 Answers1

1

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%.

user2140261
  • 7,855
  • 7
  • 32
  • 45
  • Where do I put this at? Yeah, I have headers in both sheets that take up the first row. – Solomon Closson Oct 07 '13 at 15:11
  • Added this as a macro and ran it and This removed everything in sheet1, and sheet2 stayed the same. – Solomon Closson Oct 07 '13 at 15:15
  • And your are 100% positive that not all values in sheet1 are on sheet2? I tested this code about a dozen times and it ran fine. Does your data already use filters? – user2140261 Oct 07 '13 at 15:20
  • No, both of these sheets were imported from CSV files. No filters. Sheet1 Column L has over 6,000 unique email addresses, and Sheet2 Column A only has 947 unique email addresses. – Solomon Closson Oct 07 '13 at 15:25
  • Alos if you have more columns in your data then just L that are contiguous you will have to change the Field value from 1 to the column that L represents (if the first column of your data is in column A then change the field to 12) – user2140261 Oct 07 '13 at 15:26
  • If you don't change the field then what it is doing is filter column A (field 1 of your Data) for values that are in Sheet2 Column A. – user2140261 Oct 07 '13 at 15:28
  • Added images of both sheets now. – Solomon Closson Oct 07 '13 at 15:42
  • @SolomonClosson I changed my code to reflect your images see if that works, I changed the field in the autofilter to field 12 to reflect Column L. – user2140261 Oct 07 '13 at 16:03
  • I get error again on Autofilter, and highlights this line here: `sh1.Range("L1").AutoFilter` I am using the code within a module, is that correct? – Solomon Closson Oct 07 '13 at 16:06
  • That line is to just turn off the auto-filter, try and comment it out and see if your data looks fine, it might not need that line. – user2140261 Oct 07 '13 at 16:09
  • Yeah, I believe this actually worked!!! Error occurred, but it seems to have filtered all email addresses in Sheet2 Column A out of Sheet1 Column L, and removed the rows!!! THANKS SOOO MUCH! – Solomon Closson Oct 07 '13 at 16:11