Consulting

Results 1 to 3 of 3

Thread: Excel Macro to delete rows if cell matches List of Values

  1. #1
    VBAX Newbie
    Joined
    Feb 2011
    Posts
    2
    Location

    Excel Macro to delete rows if cell matches List of Values

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Feb 2011
    Posts
    2
    Location

    Macro for deleting rows in excel

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •