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?
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?
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.
If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
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.
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.
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.Please Login or Register to view this content.
Last edited by tlafferty; 08-08-2011 at 09:40 PM. Reason: Explanatory note
Thanks. Looks simple enough; I'll give it a try and report back.
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks