I have an Excel database “tg11” with 720 names and 12 columns in worksheet “Mem”. It is updated daily. It is also saved in CSV format for a mail program. We now need to save it for two mailing lists. One list will contain all entries having “Yes” in col F “tg11_yes”. The second list will contain all entries not having “Yes” in col F “tg11_online”.
Stumped on how to delete rows having a blank cell in Col F, then reopening the database “tg11” to delete rows having “Yes” in col F.
Thanks
Gilbert
This is what the AUTOFILTER was designed for. Your macro should:
1) Turn on the AutoFilter
2) Filter column F for Yes
3) Copy the visible data to a brand new workbook
4) Save the workbook to the appropriate location/name, and close it
5) Filter column again by Custom: Does not equal: Yes
6) Copy the visible data to a brand new workbook
7) Save the workbook to the appropriate location/name, and close it
8) Copy the main sheet to a new destructible workbook
You should be able to turn on the macro recorder, record you doing that whole process once by hand, then the macro will be almost ready to use. Try it.
_________________
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!)
and
Please Login or Register to view this content.
Please Login or Register to view this content.
The Autofilter did not work. Saved the filtered list as another workbook but when opened I could select Autofilter - All to see the whole lot. I am using Office 2003.
Bob. There are 705 rows including a header row. It deleted all the rows with a blank in Col F, except for the last 14 entries.
Bob. The second one to delete all the rows with Yes did work, albeit slowly.
Tks
Jerry. Apologies I missed the "Copy". Thats fine
Tks
If you record that into a macro, post the resulting code here and we can help you clean it up into a permanent flexible macro.
You can speed it up by turning screenupdating off at the start.
Was that because the last 14 rows wer all blank in F but had data in other columns?Originally Posted by Galceran
Post your resulting "code" so we can see what you did, I'm sure something will pop out to us.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks