VBA to delete rows based on multiple criteria in multiple columns
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

  1. #11
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,298
    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

    Please respond to post #8 . The code I gave you may be noticeably faster if you have many instances of cells conforming to one or more of the multiple criteria within your 20,000+ rows. BTW: the code I posted works fine for me, but not knowing your worksheet layout, the version below which replaces the usedrange with the specific columns of interest may be preferred.
    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
        .Range("A:A,C:C,H:H").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!

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

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

    Quote Originally Posted by gli View Post
    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!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  3. #13
    New Member
    Join Date
    Jul 2010
    Posts
    39
    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 Dante, hoping you can help me, I'm working in Excel 2007.

    I am only trying to delete rows based on one criteria, being the existence of "False" in column A, which is determined successfully by the preceding script.

    However when I try to amend your script to delete only the rows which have the word "False" in column A, all blank cells are also deleted. Can you please help me to update the script for this?

    Code:
    ' Delete unneeded rows in selections
        Sheets("Selections").Select
        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("A10:O" & lr)
        For i = 1 To UBound(a)
            exists = False
            Select Case True
                Case a(i, 1) = "False": 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

    Thanks for any help you can provide.

  4. #14
    New Member
    Join Date
    Jul 2010
    Posts
    39
    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

    I forgot to mention, rows 10 to 75 need to be included in the script

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

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

    Quote Originally Posted by Danoz View Post
    I forgot to mention, rows 10 to 75 need to be included in the script
    Try this

    Code:
    Sub Delete_Rows()
        Dim lr As Long, i As Long, a, r As Range
        Application.ScreenUpdating = False
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A" & lr + 1)
        a = Range("A1:H" & lr)
        For i = 10 To UBound(a)
            If a(i, 1) = False And a(i, 1) <> "" Then
                Set r = Union(r, Range("A" & i))
            End If
        Next i
        r.EntireRow.Delete
        Set r = Nothing: Erase a
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  6. #16
    New Member
    Join Date
    Jul 2010
    Posts
    39
    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 Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub Delete_Rows()
        Dim lr As Long, i As Long, a, r As Range
        Application.ScreenUpdating = False
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A" & lr + 1)
        a = Range("A1:H" & lr)
        For i = 10 To UBound(a)
            If a(i, 1) = False And a(i, 1) <> "" Then
                Set r = Union(r, Range("A" & i))
            End If
        Next i
        r.EntireRow.Delete
        Set r = Nothing: Erase a
        Application.ScreenUpdating = True
    End Sub
    Thanks Dante, works a charm. Sorry for not replying prior to now, i have been very busy.

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

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

    Quote Originally Posted by Danoz View Post
    Thanks Dante, works a charm. Sorry for not replying prior to now, i have been very busy.
    Dont worry. Im glad to help you. Thanks for the feedback.
    Regards Dante Amor

  8. #18
    New Member
    Join Date
    Jul 2010
    Posts
    39
    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 Originally Posted by DanteAmor View Post
    Dont worry. Im glad to help you. Thanks for the feedback.
    No worries.

    I've been trying to do a similar job but for columns.

    I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in E3 to P3, A3 to D3 are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in P3.

    Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

    Code:
    ' Delete unneeded columns in selections  
    Dim LR As Long, i As Long
    Application.ScreenUpdating = False
    If MAX = 12 Then End If        -------this is where it sticks-------
    Else:
    LR = Range(Columns.Count & "3").End(xlLeft).Column
    For i = LR To 1 Step -1
    With Range(i & "3")
    If .Value = 12 Then End If
    Else:
    If .Value > MAX Then .Resize(7).EntireColumn.Delete End With Next i
    Application.ScreenUpdating = True
    I'd love some help with this script also, p's and q's.

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

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

    Quote Originally Posted by Danoz View Post
    No worries.

    I've been trying to do a similar job but for columns.

    I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in E3 to P3, A3 to D3 are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in P3.

    Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

    Code:
    ' Delete unneeded columns in selections  
    Dim LR As Long, i As Long
    Application.ScreenUpdating = False
    If MAX = 12 Then End If        -------this is where it sticks-------
    Else:
    LR = Range(Columns.Count & "3").End(xlLeft).Column
    For i = LR To 1 Step -1
    With Range(i & "3")
    If .Value = 12 Then End If
    Else:
    If .Value > MAX Then .Resize(7).EntireColumn.Delete End With Next i
    Application.ScreenUpdating = True
    I'd love some help with this script also, p's and q's.
    This is a different topic, in the previous one it is to delete rows, and this one is to delete columns. I suggest you create a new thread.
    Regards Dante Amor

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
  •