Compare Two Spreadsheets and Delete Row IF

**fieldannie22** · 06-29-2012, 02:38 PM

I have two spreadsheets, they are almost identical except for one column.

I want to be able to search Column A of sheet1 to match Column A in sheet2.
If there is a match, I would like to search Column F in sheet2 for a certain word for the same row of the match in Column A. If that word is in column F for the matching row, I would like to delete the matching row from sheet1

Any Ideas?

**FortySixAndTwo** · 06-29-2012, 03:05 PM

Hi fieldannie22, The following is not tested and I am unsure what it is you want when you find a match in the A columns but this is a start:

Option Explicit

Sub CompareColumns()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim s1Rng As Range, s2Rng As Range, oCell1 As Range, oCell2 As Range
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set s1Rng = ws1.Range(ws1.Cells(1, 1), ws1.Cells(ws1.Rows.Count, 1).End(xlUp))
    Set s2Rng = ws2.Range(ws2.Cells(1, 1), ws2.Cells(ws2.Rows.Count, 1).End(xlUp))
    
    For Each oCell1 In s1Rng
        For Each oCell2 In s2Rng
            If oCell2.Value = oCell1.Value Then
                'Not sure what you want here but this is a start'
            End If
        Next oCell2
    Next oCell1
End Sub