Deleting rows with empty cells in a specific column

**vzw1965** · 08-08-2011, 06:57 PM

I have a spreadsheet with names and addresses and other data, but only some of the records have email addresses. Is there a VBA sequence that will scan the list and delete those rows that do not have an email address?

**tlafferty** · 08-08-2011, 08:29 PM

Sure - you can write code for this, but why not use filtering to isolate rows that don't contain text "@", ".com", ".net",".org", ".biz". If that's a bit too much work, post a sample workbook and I'll take a crack at code for you.

**vzw1965** · 08-08-2011, 09:03 PM

Filtering or sorting would also work, but I am hoping to hand off responsibility to someone who's NOT a techie, so I want to automate as much as possible. I think any model spreadsheet would do for an example; all it would need is a blank in one cell in one column.

**tlafferty** · 08-08-2011, 09:38 PM

OK - so if your criteria is a blank where there should be an email address, code like the below should work. Note that it assumes that ANY text entered in the email field is valid. The downside to this is that an email address which doesn't contain the "@" symbol and a domain such as .com, .net, .org, .biz etc. will still be assumed to be a valid address.

Please Login or Register  to view this content.

Also, if your email field is something other than column A you will want to modify the above code. Be aware that it runs on whatever the active sheet is when you start the macro.

**vzw1965** · 08-09-2011, 10:14 AM

Thanks. Looks simple enough; I'll give it a try and report back.

**vzw1965** · 08-09-2011, 10:55 AM

I tried the routine you suggested and it works as advertised, BUT it doesn't delete all the rows, even if I ensure they are empty. In fact, I need to run it three times in a row to delete all rows that don't have a email address. I've scrubbed all the cells that are empty before running the routine, but it still misses the same rows each time, but running it a second time eliminates most of those, and running it a third finally gets them all. Any thoughts about what might cause this?