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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

lwharrell

Thread Starter
Joined
Sep 25, 2008
Messages
4
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
 
Joined
Jun 17, 2002
Messages
2,554
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.



Code:
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
 
Joined
Jul 1, 2005
Messages
8,546
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.
 
Joined
Oct 20, 2004
Messages
7,837
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.
 
Joined
Jul 1, 2005
Messages
8,546
@ 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).
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top