Help with MS Excel - Finding & Deleting several records at once.

[lwharrell]

I have a 70,000 person Mailing List Database in MS Excel. After a mailout I have usually 1000 to 2000 bounced emails returned to me . I would like to remove the corresponding records. I can isolate the bounced emails into a .txt file or .csv file but cant figure how to get MS Excel to find all and remove all the corresponding records (at once) without having to find and delete one at a time. Can you help me? Larry

 

[Ziggy1]

If there is a column that has a "Flag" indicating which records you want to Delete, then you could use this code to delete those records.

in this example it is checking Column "C" and if equal to letter "D" it will delete the row, you can change the column and the value to check for your purposes.

Public Sub deleteRow1()

Dim x As Long, Y As Range
        
Set Y = ActiveSheet.UsedRange.Rows

For x = Y.Rows.Count To 1 Step -1
    'adjust to match the column you want to check
    If Range("C" & x) = "D" Then
        Y.Rows(x).EntireRow.Delete
    End If
Next x


End Sub

 

[bomb #21]

Looping through 70K records might be a bit strenuous.

Personally I'd use a helper column with a formula; something like:

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$10,0)),1,0)

to return 1 for records not in the bounce list (Sheet2!A1:A10) and 0 for those that are. Followed by sorting on B to shunt the bounced records to the top, for deletion.

 

[slurpee55]

ASAP Utilities ( http://www.asap-utilities.com/ ) is a handy free add-in for Excel, and one of the things it can do is find and delete all duplicates. So you could just copy all the bounced emails to the bottom of the list and run that and that would be the end of them. Alternatively, it can just highlight them all, so you could run it and cut and paste the bad emails into another sheet.

 

[bomb #21]

@ Larry.

Received your email. Using XL2K, so I can't receive a file with 70K+ rows.

Upload a sample file -- just a few dummy rows with any sensitive data removed -- & I'll look at why you can't adapt the formula (as long as the file is .xls rather than the new format).