VBA to delete rows based on multiple criteria in multiple columns
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA to delete rows based on multiple criteria in multiple columns

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to delete rows based on multiple criteria in multiple columns

    Hello all,

    I have a large spreadsheet that is only 8 columns wide but 20k+ rows deep. I am looking for code that will efficiently delete rows based on multiple criteria. Right now I am making the deletions by hand but am looking for a way to automate the process.

    Below is the criteria that should cause a row to be deleted based on the column in question. Any of these criteria may or may not be present in the data.

    Column A - any row with a cell that contains the following numbers: "998" and "999".
    Column C - any row that contains the word "CLOSED". This would be any instance of the word "CLOSED", whether it is a stand alone word or not. On occasion it will have other text right up next to it, so the code would need to account for that and still delete the row.
    Column H - any row with a cell that contains the following numbers: "28" and "34".

    There are more numbers that need to be deleted, but I figured I would save you guys some keystrokes and just repeat the code as needed for the missing numbers once I know what the setup of the code will look like. Thank you for any help you can provide!

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,289
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    Try this
    I put some examples so you can add more numbers.

    Code:
    Sub Delete_Rows()
        Dim lr As Long, i As Long, a, r As Range, exists As Boolean
        Application.ScreenUpdating = False
        lr = Range("A" & Rows.Count).End(xlUp).Row
        a = Range("A1:H" & lr)
        For i = 1 To UBound(a)
            exists = False
            Select Case True
                Case a(i, 1) = 998, a(i, 1) = 999: exists = True
                Case UCase(a(i, 3)) Like "*CLOSED*": exists = True
                Case a(i, 8) = 28: exists = True
                Case a(i, 8) = 34: exists = True
            End Select
            If exists Then
                If r Is Nothing Then
                    Set r = Range("A" & i)
                Else
                    Set r = Union(r, Range("A" & i))
                End If
            End If
        Next i
        r.EntireRow.Delete
        Set r = Nothing: Erase a
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Jun 2006
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    [QUOTE=JoeMo;5296796]For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?[/Q]

    Sorry, that should be an OR. So if column A has 998 or 999, delete. Same with column H.

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,289
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    [QUOTE=gli;5296888]
    Quote Originally Posted by JoeMo View Post
    For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?[/Q]

    Sorry, that should be an OR. So if column A has 998 or 999, delete. Same with column H.
    Try this on a copy of your worksheet. Be aware that the presence of any one of the criteria, as part of or the entirety of a cell will cause deletion, and there's no undoing it. For example, a row having a cell containing the string ABC03998654DEF will be deleted.
    Code:
    Sub gli()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Columns("A").Replace what:="*998*", replacement:="#N/A", Lookat:=xlWhole
        .Columns("A").Replace what:="*999*", replacement:="#N/A", Lookat:=xlWhole
        .Columns("C").Replace what:="*CLOSED*", replacement:="#N/A", Lookat:=xlWhole
        .Columns("H").Replace what:="*28*", replacement:="#N/A", Lookat:=xlWhole
        .Columns("H").Replace what:="*34*", replacement:="#N/A", Lookat:=xlWhole
        On Error Resume Next
        .UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    Board Regular
    Join Date
    Jun 2006
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    JoeMo, when I use your code the affected cells with matching criteria are being replaced with "#N/A", but the rows aren't being deleted.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    Did you try the macro of post # 3?
    Regards Dante Amor

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,289
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    Quote Originally Posted by gli View Post
    JoeMo, when I use your code the affected cells with matching criteria are being replaced with "#N/A", but the rows aren't being deleted.
    Comment out the On Error Resume Next line and try again - is there an error on the specialcells line?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    Board Regular
    Join Date
    Jun 2006
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    Hi DanteAmor, I tried your code just now and it appears to work as needed. I'm looking closer at the data to make sure, but all indications are that it did the job nicely and accurately. Thanks!

  10. #10
    Board Regular
    Join Date
    Jun 2006
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

    Hi Joe, thank you for your efforts. Since the other code worked I'm going to use it, but I greatly appreciate your help and I thank you for your time.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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