How to auto-delete rows that contain specific word?

dylansmith · #1 10-13-2018, 10:33 PM

I have a huge email database that I need to clean, and it contains many fields, such as name/phone/email/address etc.

How do I specify a set of domains that will be excluded from my mailing list? E.g. I want to remove all entries containing the emails ending with xxx@maildrop.com.

Thanks in advance for your help!

jeffreybrown · #2 10-14-2018, 02:52 PM

You would need to use some VBA for this. Something like this where email addresses are in column D

Code:

Sub FilterDelete()
    With Range("D1", Range("D" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:="*@maildrop.com"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Note: Please first test on a copy of your data

ArviLaanemets · #3 10-15-2018, 12:05 AM

Attached workbook contains an example, where a formula in column returns TRUE when searched string is found in certain column (it looks like you can't start the search string with special character like "@", but mostly this is not needed).

You can set autofiler for column IsMatch, and e.g. delete filtered rows - or do whatever you want with them.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Keeping just the rows with specific word (BUT KEEP THE TITLE)	Kopko	Word VBA	20	06-05-2017 04:44 PM
Excel VBA macro to copy specific rows and delete it	pourmalla	Excel Programming	2	04-02-2017 12:09 AM
Macro to delete rows not containing specific text	Alimou	Excel Programming	4	02-06-2017 07:35 AM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM
Delete rows using checkbox in word	deboer	Word VBA	5	06-22-2014 05:21 AM