Compare two sheets and delete row if match found

**Steinwall** · 05-06-2011, 11:33 AM

Hi,

This is my first post to this forum, but I just couldn't figure out this Macro and I was hoping someone could help.

I have two sheets in one workbook that I need a macro to look for matching rows of information between the two and delete a row in Sheet 1 if a match is found. I have attached the two sheets here so feel free to take a look.

We need to loop through each row in Sheet 1 matching 4 criteria (Due Date, Part Number (also known as AKA), PO, and Qty) from a row in Sheet 2. If all four match, delete the row in Sheet 1 and move to the next. If all four do not match any rows in Sheet 2, move to the next row without deleting in Sheet 1. Also, if there is a row in Sheet 2 that is not in Sheet 1 - it should be turned red.

I hope that makes sense - it's a pretty simple VBA I think, but I am just not that skilled in VBA yet to get it to work out so I was hoping for some guidance from the more experienced...

Please help!

Thanks!

**mojo249** · 05-06-2011, 08:38 PM

Should there be any matching entries in the workbook you provided? I wrote the following but it doesn't find any matches - not sure if it's the code or the data...

Dion

Sub DeleteRows()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim varLastRow1 As Long
Dim varLastRow2 As Long
Dim varRow1 As Long
Dim varRow2 As Long

Set ws1 = Sheets("Sheet 1")
Set ws2 = Sheets("Sheet 2")
varLastRow1 = ws1.Range("A500000").End(xlUp).Row
varLastRow2 = ws2.Range("A500000").End(xlUp).Row

For varRow1 = varLastRow1 To 4 Step -1
    For varRow2 = 2 To varLastRow2
        If ws1.Cells(varRow1, 2).Value = ws1.Cells(varRow2, 1).Value Then
            If ws1.Cells(varRow1, 5).Value = ws1.Cells(varRow2, 3).Value Then
                If ws1.Cells(varRow1, 4).Value = ws1.Cells(varRow2, 2).Value Then
                    If ws1.Cells(varRow1, 6).Value = ws1.Cells(varRow2, 4).Value Then
                        ws1.Select
                        Rows(varRow1).EntireRow.Delete
                    End If
                End If
            End If
        End If
    Next varRow2
Next varRow1
        

End Sub

**Steinwall** · 05-10-2011, 11:46 AM

Hi Dion,

Thank you so much for replying! I didn't get any matches either and a majority of the rows are matches, but the values may not be in the same row and they definitely aren't in the same column (even though I could change that if it would be easier for programming)... so that could be why it didn't work.

Any suggestions for a new code given that it will need to be a bit more "investigative?"

Kaci

**mojo249** · 05-12-2011, 05:01 PM

Kaci

I think you've lost me. Picking a sample of entries on Sheet1:

Row 9 = PO # 4500035085
Row 93 = PO # 4500632484
Row 184 = PO # 4503713299

None of these PO #s appear on sheet 2. In fact, none of the POs on Sheet 1 are on Sheet 2 so you won't get any matches.

Do you want ALL of the conditions to be matching (Due Date, Part Number (also known as AKA), PO, and Qty) or ANY of them to be matching.

Dion

**Steinwall** · 05-13-2011, 03:25 PM

Sorry! I should have mentioned that the first 302 lines will not have matches...

But the macro intellegence would still work the same - if the PO does not have a match on the second sheet, then it needs to simply stay/be ignored. We only want to delete any line that has a perfect match on both pages (perfect match meaning that the PO number, due date, qty and AKA number match). If it does not have a perfect match (or doesn't even appear at all on the other page), then the macro needs to recognize that, ignore that line, and move to the next line.

Does that make more sense?

Thanks for your help!!