|
|
Can I delete an entire row if condition is not met? |
|
Author |
Message |
pitchblack
|
Posted: Wed May 03 02:31:01 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
I'm working on Outlook 2003 and Word 2000 which are not compatible. I have an
Excel Sheet which are my contacts from Outlook and I want to re-work it to
have only a certain category left.
I need a macro that does
- search a certain column
- deletes the row if it does NOT find a certain condition
Ideal would be a little pop up window to enter which category I want left.
I can't figure it out. Can anybody help? Thanks.
Excel43
|
|
|
|
|
Bob
|
Posted: Wed May 03 02:31:01 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
The easiest way is to add autofilter to that column,
Data>Filter>Autofilter..., and then when you click the dropdown, you see all
the values, and if you select one, only those rows matching are then
visible, so you can select them all (F5>Special, click Visible Cells Only),
and then delete the, (Ctrl & -, and tick Entire row).
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
> I'm working on Outlook 2003 and Word 2000 which are not compatible. I have
an
> Excel Sheet which are my contacts from Outlook and I want to re-work it to
> have only a certain category left.
>
> I need a macro that does
> - search a certain column
> - deletes the row if it does NOT find a certain condition
>
> Ideal would be a little pop up window to enter which category I want left.
>
> I can't figure it out. Can anybody help? Thanks.
|
|
|
|
|
Christine
|
Posted: Wed May 03 14:25:02 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
Hi Bob,
Thanks but that won't work because in the file I export from Outlook, the
column contains ALL categories so the column would look like this:
VIP;xmas;SalesMeeting
xmas
SalesMeeting;xmas;Supplier
So let's say I need the people for the Sales Meeting, they can be anywhere
in that cell so filtering doesn't work.
I wish it could be so easy ;-)
|
|
|
|
|
Bob
|
Posted: Wed May 03 17:07:53 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
You do have the contains clause in the filter options if you choose custom.
Otherwise you might need a helper column and use a formula to identify
offending, or otherwise, rows. Give a bigger data example and what you want
to weed out if the filter/custom option doesn't work.
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
> Hi Bob,
>
> Thanks but that won't work because in the file I export from Outlook, the
> column contains ALL categories so the column would look like this:
>
> VIP;xmas;SalesMeeting
> xmas
> SalesMeeting;xmas;Supplier
>
> So let's say I need the people for the Sales Meeting, they can be anywhere
> in that cell so filtering doesn't work.
>
> I wish it could be so easy ;-)
|
|
|
|
|
Christine
|
Posted: Wed May 03 18:42:01 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
Hi Bob,
There's no 'contain' in the filter option, only have Excel 2000 so maybe
that's for the future ... when my Word and Outlook are compatible again, I
assume.
Whata function do you use for that? Just had a look in help and can't find
anything.
Here's a bigger sample and I want to have the 'ProductLaunch' people staying:
Diary;ProductLaunch;private;xmas
Diary;ProductLaunch;private
Diary;ProductLaunch
ProductLaunch;Diary;private
Diary;ProductLaunch
Diary;ProductLaunch
Diary;ProductLaunch;xmas
Diary;ProductLaunch;private;xmas
xmas
Calendar;public;Newsletter;xmas
public;Newsletter
xmas;Newsletter
NPS 2006;Newsletter
CT User;DiaryT;public
xmas;public;Newsletter
CT User;DiaryT;private
CT User;DiaryT;public
CT User;VIP;public
CT User;NPS 2006;public;VIP;xmas
xmas;VCT;CT User;CT;DiaryT;NPS 2006;public;Newsletter
CT User;DiaryT;public
CT User;DiaryT;public
xmas;CT User;VIP;DiaryT;NPS 2006;private;Newsletter
CT User;DiaryT;private
CT User;DiaryT;private;Newsletter
CT User;DiaryT;private
CT User;DiaryT;private
CT User;DiaryT;private
xmas;CT User;DiaryT;private;Newsletter
Thanks for your help!
|
|
|
|
|
Pete_UK
|
Posted: Wed May 03 19:28:58 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
I have "contains" in my XL2000 (and I had it in XL97) - when you select
Custom from the pull-down on the filter, you have to scroll down to
find "contains".
Hope this helps.
Pete
|
|
|
|
|
Christine
|
Posted: Wed May 03 19:38:01 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
You're a star. Bob, pete, sorry for being so blind! This should be so easy now.
Thanks for your help!
Christine
> I have "contains" in my XL2000 (and I had it in XL97) - when you select
> Custom from the pull-down on the filter, you have to scroll down to
> find "contains".
>
> Hope this helps.
>
> Pete
>
>
|
|
|
|
|
Bob
|
Posted: Thu May 04 03:16:15 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
If Pete is in the UK, he is a late bird <G>
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
> I have "contains" in my XL2000 (and I had it in XL97) - when you select
> Custom from the pull-down on the filter, you have to scroll down to
> find "contains".
>
> Hope this helps.
>
> Pete
>
|
|
|
|
|
Pete_UK
|
Posted: Thu May 04 03:47:27 CDT 2006 |
Top |
worksheet functions >> Can I delete an entire row if condition is not met?
Yes, I fly with the owls ! <bg>
Pete
|
|
|
|
|
|
|