Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Christine
 
Posts: n/a
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Can I delete an entire row if condition is not met?

The easiest way is to add autofilter to that column,
DataFilterAutofilter..., 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 (F5Special, click Visible Cells Only),
and then delete the, (Ctrl & -, and tick Entire row).

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Christine" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Christine
 
Posts: n/a
Default 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 ;-)
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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)

"Christine" wrote in message
...
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 ;-)



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Christine
 
Posts: n/a
Default 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!


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Christine
 
Posts: n/a
Default 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

"Pete_UK" wrote:

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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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)

"Pete_UK" wrote in message
ups.com...
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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default Can I delete an entire row if condition is not met?

Yes, I fly with the owls ! <bg

Pete

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Easiest way to delete blank cells in column (not entire row) sramsey Excel Discussion (Misc queries) 4 February 16th 06 05:28 PM
How do I delete duplicate records from an entire Excel workbook? Steven B. Excel Discussion (Misc queries) 0 December 6th 05 11:32 AM
Delete ENTIRE row after updating info in worksheet. naiveprogrammer Excel Worksheet Functions 0 September 21st 05 09:27 PM
can I delete entire row if adjacent cell formats differ? ksukid Excel Worksheet Functions 0 May 19th 05 09:28 PM
how do I delete all rows that match a condition? djhs63 Excel Worksheet Functions 5 March 16th 05 04:55 PM


All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"