Macro to delete specific emails/data from excel spreadsheet?

[kholden1]

Good Afternoon!

I am trying to figure out how to delete specific email addresses I've found as "bad emails" from a 500 line excel spreadsheet. I'd preferably like to have it delete the whole row where the email address is attached but at least have it delete the email. I can then filter by blanks and delete them off of my list that way. Does anyone have any possible idea's on where I can start!? If it's not possible, I could instead use Company names to find the data and delete. I appreciate any and all help!

 

[Joe4]

Can you show us an example of your data (first removing/changing any sensitive information, like real email addresses or personal identification), so we can see how it is all structured and see what you want to remove?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kholden1]

I wish I could use the XL2BB tool! It seems right up my alley! The pictures shows some of the data I am using, except like I mentioned, I have over 500 lines. In the end, I'm really only using the email addresses and first name. I have a second list of email's I need to take out of the original list so I can keep the good emails for a mail merge.

 

[kholden1]

@Joe4 Does this picture help at all or do you need more information? Would you mind guiding me on where to start with a VBA, please?

 

[Joe4]

Sorry, I missed your response yesterday.

So, in looking at the sample you included, how is it determined which lines to keep and which to delete?

Also, you show your office version as 365 and 2007. Which one are you using for this purpose?
The method I use may be dependent upon that.

 

[kholden1]

It's fine! I really appreciate you taking a look for me. I'm using 365. I have another list of email errors which came back undeliverable. Instead of going one by one in the list shown, finding and deleting. I was thinking there was a way to put all of the data (emails or by company) in the VBA and deleting it that way?

 

[Joe4]

If you have a list of bad emails, here is one way you could do it:
- Insert a new sheet that lists all the bad email addresses you want remove.
- Do a simple VLOOKUP, XLOOKUP, INDEX/MATCH, or COUNTIF formula to match the two emails (i.e. you could do a simple COUNTIF to count the number of time each email is found on the "bad" list
- Filter out the bad email addresses (note that Excel 365 has a new FILTER function that allows you to create a brand new list, filtering out the records you do not want
See: FILTER function - Microsoft Support