LinkBack URL
About LinkBacks
I have a mailing list that was produced from a database but I only want to send to people in rows that dont have an email address in column F.
I need a formula that will delete all rows that contain an email address in
Col F.
Can anyone help.
Thanks
I have a mailing list that was produced from a database but I only want to send to people in rows that dont have an email address in column F.
I need a formula that will delete all rows that contain an email address in
Col F.
Can anyone help.
Thanks
Is there anything else that's going to be anywhere in column F, or would it be safe to say:
If the cell at row x, column F is not blank, delete row x.
mew?
If so, run something like:
mew!
Please Login or Register to view this content.
That's a macro by the way.
Just lemme know if you need to know what to do with that.
wait...
that sounded a lot dirtier than it was supposed to...
Last edited by mewingkitty; 11-05-2009 at 06:34 PM.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
I am sorry, having trouble working out what you are asking! I have had a really bad day!
What I think you are asking is if there is any other info in Col F. I have something like 1000 rows, and 500 of them have an email address in them and the other 500 are blank.
I want to delete any that are not blank and keep the rows that are blank in col F - Yes thats it! Sorry had to write/type it down to work out, think I need to go have a sleep.
Thanks
I dont understand the formula. I think it is beyond my knowledge, you are writing things like the word 'Dim' I am Dim because I dont know it. I know some of the basic formulas and some of the IF formulas but thats it. When you write VBNullstring, then - I am lost :-( Sorry but thanks for trying with a dim wit here!
Oh, don't be so hard on yourself.
We all started with =A1+B1
In your workbook:
Click on your windows button, the one you use to save a file.
On the bottom of that drop down window, click "Excel Options"
In the "Popular" tab, there should be an option that says "Show the developer tab in the ribbon". Check that box
Close the options screen
On the top of your window there will now be a tab called "Developer" on the far right. Click on that tab.
Click on the button that says "Macros"
Enter a name for your macro, call is something simple with no spaces, like: DeleteRows
Click on the button on the right of that window that says "Create"
Copy and paste what I've posted in that box there with all the gibberish language. Paste it in the row inbetween "Sub DeleteRows()" and "End Sub"
That should now look like this:
Now on that same screen, above the white area you just pasted that into, there's a little green triangle that looks like a play button. When you mouse over it, it should say "Run Sub/Userform (F5)"Please Login or Register to view this content.
Press that button.
Zing.
Note that you should save your workbook under a new name, as this can not be undone. If you save over your original workbook, those email addresses that we just deleted are toast. If you don't need them EVER, then go ahead and save over it.
Hope that helps.
mew!
Last edited by mewingkitty; 11-05-2009 at 06:58 PM.
Mew...be sure to include "how to use a macro" instructions when you post macros in the non-programming forums. And the code should probably be all-inclusive...names and all.
======
How to use the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
A non-macro way to do it with Excel's AutoFilter.
1) Make sure the top of the data has "titles" in it, if not, add titles.
2) Click on the title row (row1?) and select Data > Filter > AutoFilter to turn it on
3) Click on the dropbox for column F and select NON-BLANKS
4) Highlight all the rows you still see and delete them all (Edit > Delete
5) Turn off the Autofilter, same way you turned it on.
Now you have only rows that are blank in column F left.
I believe those are all Excel 2007 terms and features, not available in Excel 2000.Originally Posted by mewingkitty
Alright so I'm an idiot.
This is not late breaking news. :P
sry!
Not at all. I forget to include those instruction in the non-programming forums all the time and have to go back and add them. Go team...
Mewingkitty, you're a genious not an idiot, in my opinion :-)
Ok, I guessed your were advising on 2007 and although my boss is getting me that for Christmas, I am still on 2000. I managed to work my way through the 2000 with your instructions and I have deleted all my email lists, hopefully thats all!!! I went Tools, Macros, record new macro and gave it the name you advised, a shortcut key and pressed ok. Then I hit Alt + F8 (seen that somewhere earlier) When it brought up the box I pressed either edit or run and it brought up the macro so I could delete what was there and copied and pasted you info so it all looked the same! The pressed the play button on the menu bar and it all just happened :-)
I wonder if I can ask what it all meant though, if you have time! I was looking for something like deleterows ???? F1:F1000 That macro stuff doesnt even mention my col. Are you able to explain it or is this too complicated and I just need to continue my learning process?
Now off to try understand what the other guy is saying about programming!
Thanks so much for your help, I will write it all down.
Nikky
Ok guys, I got it all now, even using jbeaucaire instructions and with the filter.
Thank you so much, both of you, very helpful and very much appreciated.
Nikky
Glad to hear it.
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks