Results 1 to 6 of 6

Thread: Delete Remove Rows By Criteria VBA Excel

  1. #1
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13

    Delete Remove Rows By Criteria VBA Excel

    I need a macro to scan a column of a spreadsheet with over 100,000 lines, validate whether a string "abcdefg" OR a string "hijklmn" in a cell. If there is no two strings in the cell, the line corresponding to the cell should be excluded.

    Can you help?

    Detail: the cells containing the text strings have beyond string. For example, a cell has value "abcdefgh123" and should not be deleted.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    1. replace all blank cells with a special character (hit F5 > Special > Blanks > OK. Now type | and hit Ctrl + Enter )
    2. hit ctrl + H find what: abcdefg leave empty the filed 'Replace with'
    3. repeat # 2 for other string
    4. hit F5 > Special > Blanks > OK. Alt > H > D > R (XL 2007 later) or Menu key (between Windows key and Ctrl key) > D > R
    5. hit ctrl + H > find what: | leave empty the filed 'Replace with'
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    HI
    empty lines I had already treated.
    My problem now is scan the entire column (the report that the application generates and I copied to your clipboard windows groups all fields in a single column). In each cell, the macro or formula shall examine whether there are two distinct strings, and the text of each of the cell strings is not restricted to demand, since the report played all fields in a single column. If you do not find the strings, the routine deletes the line.
    I wanted a macro to facilitate the process, but I know you can do this using nested functions. But there are more than 100,000 lines.

  4. #4
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi.
    the hint of F5 earned rest. Using F5, Find and ISERROR functions, could filter all records quickly.

    Thank you!

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    In case you are still looking for a macro to do this, try

    Code:
    Sub RemoveUnwantedRows()
    
        Dim lng As Long
        
        For lng = 100000 To 1 Step -1
            If InStr(1, Cells(lng, "A"), "abcdefg") + InStr(1, Cells(lng, "A"), "hijklmn") = 0 Then
                Rows(lng).Delete
            End If
        Next lng
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi Excel Fox
    Thank you!!!

Similar Threads

  1. Delete Rows Based on Conditions
    By AbiG2009 in forum Excel Help
    Replies: 6
    Last Post: 12-26-2018, 01:24 PM
  2. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  3. Delete Rows
    By ayazgreat in forum Excel Help
    Replies: 2
    Last Post: 12-13-2012, 11:48 AM
  4. Remove or Hide or Delete Negative Bubbles
    By technicalupload in forum Excel Help
    Replies: 6
    Last Post: 10-26-2012, 04:32 PM
  5. Delete Empty Rows
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-28-2011, 02:13 AM

Posting Permissions

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