PDA

View Full Version : Excel Macro to delete rows if cell matches List of Values



User2011
02-17-2011, 02:36 PM
I'm trying to create an excel macro to automate a delete process in a worksheet.
It must:

1. Read a list of Numbers from a text file on the local Drive, or I can paste the numbers to another worksheet.

2. find if each number exists anywhere in the worksheet and delete all rows containing the value.

3. Continue to the end of the List of values.

- The numbers are generally 11 - 13 digits
- They exist in columns E or K in the worksheet
- They may exist multiple times in the worksheet.

I've created a few macros to automate my work and this would really help. I'm just not sure how to approach this.

Bob Phillips
02-17-2011, 03:22 PM
Paste the values into a worksheet range, let's say Sheet3!A1:A10

In the data sheet, in say L2, add this formula

=OR(ISNUMBER(MATCH(E2,Sheet3!$A$1:$A$10,0)),ISNUMBER(MATCH(K2,Sheet3!$A$1:$ A$10,0)))

and copy down.

Put some text in L1 then filter the column for TRUE and delete all matching rows.

User2011
02-18-2011, 07:00 AM
Thanks a lot for the response and help!

I've successfully used this method and I can make it work. It is a bit more manual than I was hoping for as I must
1. Create an additional sheet paste the values
2. Copy the formula down the page in the data sheet
3. Copy and paste the resulting VALUES i.e. True/False into another column - using 'paste special/values'
4. Sort by the new column of Values
5. Manually delete the rows.

I will attempt to create the macro with these steps.