1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by lwharrell, Sep 25, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. lwharrell

    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
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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
     
  3. bomb #21

    bomb #21

    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.
     
  4. slurpee55

    slurpee55

    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.
     
  5. bomb #21

    bomb #21

    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).
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/753255

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice