Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-13-2018, 10:33 PM
dylansmith dylansmith is offline Windows 8 Office 2013
Optimizer
 
Join Date: Nov 2011
Posts: 105
dylansmith is on a distinguished road
Default How to auto-delete rows that contain specific word?


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!
Reply With Quote
  #2  
Old 10-14-2018, 02:52 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 478
jeffreybrown will become famous soon enough
Default

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
Reply With Quote
  #3  
Old Yesterday, 12:05 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 299
ArviLaanemets is on a distinguished road
Default

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.
Attached Files
File Type: xlsx SearchString.xlsx (9.9 KB, 3 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 01:16 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft