+ Reply to Thread
Results 1 to 8 of 8

loop multiple criteria & delete matching rows?

Hybrid View

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    96

    loop multiple criteria & delete matching rows?

    Morning All,

    Attached is a simplified spreadsheet with my intentions... ive had no luck with initiating something remotely close to achieving this and would like some assistance.
    Objective: If the highlighted criteria from sheet2 matches the corresponding columns in sheet1, remove that entire row.

    In my head its a simple objective but the two potential loops are throwing me off.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: loop multiple criteria & delete matching rows?

    Are the check# values unique? Meaning that column will never have duplicates?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    11-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: loop multiple criteria & delete matching rows?

    They may have duplicates - some checks are comprised of multiple payments so yes, good question.

  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: loop multiple criteria & delete matching rows?

    Maybe:

    Sub Quint6778z()
    Dim i As Long, x As Range, y, z As String, ws As Worksheet
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    Set ws = ActiveSheet
    With ws
    ReDim y(2 To .Range("A" & Rows.Count).End(3).row)
    For i = UBound(y) To LBound(y) Step -1
        z = .Cells(i, "B")
        Set x = Sheets("DATACRITERIA").Columns(1).Find(.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
            If Not x Is Nothing Then
                If x.Offset(, 1) = z Then .Rows(i).Delete
            End If
        Set x = Nothing
    Next i
    End With
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    End Sub

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: loop multiple criteria & delete matching rows?

    Ok, since we have to delete, we need to store the rows to delete in a range because if we delete on the fly we lose the range myMatch... give this a try:

    Sub multimatch()
    
    Dim wsc As Worksheet 'the sheet to match from
    Dim wsd As Worksheet 'the sheet to delete from
    Dim lr  As Long
    Dim i As Long
    Dim myMatch As Range
    Dim match_address As String
    Dim searchrange As Range
    Dim delete_range As Range
    
    Set wsc = Worksheets("DATACRITERIA")
    Set wsd = Worksheets("PULLDATAFROM")
    lr = wsc.Range("B" & Rows.Count).End(xlUp).Row
    Set searchrange = wsd.Range("B:B")
    For i = 2 To lr 'starts at row 2 since 1 is headers
        Set myMatch = searchrange.Find(what:=wsc.Range("B" & i).Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        If Not myMatch Is Nothing Then
            match_address = myMatch.Address
            If wsd.Range("A" & myMatch.Row).Value = wsc.Range("A" & i).Value Then Set delete_range = wsd.Range("A" & myMatch.Row).EntireRow
        Else
            GoTo not_found
        End If
        Do
            Set myMatch = searchrange.FindNext(myMatch)
            If wsd.Range("A" & myMatch.Row).Value = wsc.Range("A" & i).Value Then
                If delete_range Is Nothing Then
                    Set delete_range = wsd.Range("A" & myMatch.Row).EntireRow
                Else
                    Set delete_range = Union(delete_range, wsd.Range("A" & myMatch.Row).EntireRow)
                End If
            End If
        Loop While Not myMatch Is Nothing And myMatch.Address <> match_address
    If Not delete_range Is Nothing Then delete_range.Delete shift:=xlUp
    not_found:
    Next i
    
    End Sub

  6. #6
    Registered User
    Join Date
    11-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: loop multiple criteria & delete matching rows?

    @JDavis & Arkardi - Both of your solutions achieved the desired result. I'm amazed that, before i finished my coffee, there is a solution to my much attempted problem. I will let you know once its fine-tuned. Amazing as always, thank you both again.

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: loop multiple criteria & delete matching rows?

    Thanks for the feedback Quit6778, and for marking the thread as solved
    And also for the kind words and rep!

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

    Re: loop multiple criteria & delete matching rows?

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

+ 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. [SOLVED] Objective: delete rows based on multiple criteria matching
    By Quint6778 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2017, 05:25 PM
  2. [SOLVED] DELETE Rows in SQL db table matching criteria via EXCEL VBA
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-10-2016, 07:03 PM
  3. Macro to delete rows based on two different criteria matching
    By Cpower17 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2014, 05:43 PM
  4. Loop through rows and delete based on criteria
    By yolandaro30 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-27-2012, 12:23 PM
  5. Within a loop, need to copy all rows from one sheet to another upon matching criteria
    By ripvanbrown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2012, 03:52 PM
  6. [SOLVED] Mark or Delete Rows based on Matching Multiple Criteria in another worksheet
    By clearbluez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 02:38 PM
  7. delete rows not matching certain criteria
    By sanketgroup in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2011, 04:04 PM

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