Can I delete an entire row if condition is not met?

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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 Phillips]

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)

 

[Guest]

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]

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

 

[Guest]

You're a star. Bob, pete, sorry for being so blind! This should be so easy now.

Thanks for your help!

Christine

 

[Bob Phillips]

If Pete is in the UK, he is a late bird <G>

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Pete_UK]

Yes, I fly with the owls ! <bg>

Pete