+ Reply to Thread
Results 1 to 7 of 7

Delete Row if Cell Contains Specific Values (Multiple Criteria)

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Delete Row if Cell Contains Specific Values (Multiple Criteria)

    I've tried to find ways to delete an entire row if a cell contains a specific value and I've found some that use AutoFilter and some that use loops but none of which would allow me to specify more than one criterion.

    I have a list of members for our business and some of these members are actually staff and employees. I can identify the staff/employees by their member numbers and I would like to delete entire rows containing any information about them so I can focus only on the customer base.

    I have a list of 22 staff/employee member numbers and would like to get rid of any row in which the member ID column contains any of those 22 numbers.

    How would I macro this?

  2. #2
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    Hi,

    Check the below link, it shows to look up values in range and delete entire row if found.

    http://stackoverflow.com/questions/1...specific-value


    Regards,
    Paresh J
    Click on "* Add Reputation" as a way to say thanks

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    So, if I were to grab the code from there which searches for "dog" and deletes that row, but try to add "cat" and "mouse" like below:

    Sub DeleteRows()
       Dim c As Range
       Dim SrchRng
    
       Set SrchRng = ActiveSheet.UsedRange
       Do
           Set c = SrchRng.Find("Dog", "Cat", "Mouse", LookIn:=xlValues)
           If Not c Is Nothing Then c.EntireRow.Delete
       Loop While Not c Is Nothing
    End Sub
    Then that would work?

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    Probably not the best way.

    Sub efarkouh()
    Dim i As Long
    For i = Range("A" & Rows.count).End(3).Row To 2 Step -1
        Select Case Range("A" & i).Value
            Case Is = "Employee1", "Employee2", "Employee3", "Employee4", "Employee5" 'and so on
            Rows(i).Delete
        End Select
    Next i
    End Sub

  5. #5
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    Hi,

    You can Loop your values to be search, first search for Dog if found delete row and then search for Cat and so on..... Point is you need to loop your search values.


    Regards,
    Paresh J

  6. #6
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    John, that worked brilliantly.

    Thank you gentlemen.

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete Row if Cell Contains Specific Values (Multiple Criteria)

    You're welcome. Glad to help out and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 08-04-2013, 09:49 AM
  2. Delete rows which do not meet specific criteria (text values)
    By dim06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2012, 09:54 AM
  3. Replies: 6
    Last Post: 02-07-2012, 08:21 PM
  4. Delete multiple rows of data based on criteria in a single cell
    By slaga9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2011, 03:41 AM
  5. How to delete multiple values in a column based on Criteria
    By ravi3cha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2010, 07:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1