# delete row if 2 cells match in excel



## happycats (Oct 23, 2008)

i'm trying to write an excel macro that compares a value in column A on Sheet2 to a value in column B in sheet1. if a match, then delete the 
delete entire row on sheet1. 

can anyone help?


----------



## David M58 (May 20, 2008)

The following example assumes that row 1 on each sheet contains headers, and that the actual data starts with row 2.


```
Sub remove()
    Dim sr1 As Long
    Dim sr2 As Long
    For sr1 = Sheets("Sheet1").Range("B2").CurrentRegion.Rows.Count To 2 Step -1
        For sr2 = 1 To Sheets("Sheet2").Range("A2").CurrentRegion.Rows.Count
            If Sheets("Sheet1").Cells(sr1, 2).Value = Sheets("Sheet2").Cells(sr2, 1).Value Then
                Sheets("Sheet1").Rows(sr1).EntireRow.Delete
                Exit For
            End If
        Next sr2
    Next sr1
End Sub
```
The code goes through each value in column B on Sheet1 and looks for a match in column A on Sheet2. If the value is found on Sheet2, then the row on Sheet1 containing that value is deleted. Modify as necessary.


----------



## ancroley (Feb 25, 2009)

This macro would be perfect for me if instead of deleting the row, I add the numbers in the row. So I want to match A2 on one sheet to column A in another sheet (the person may be listed multiple times so I want this to add all of the rows where that persons name matches. Make sense? 

Now, what if I don't want it to do the whole row...but just 7 cells in the row? So, if A2 matches any cells in A of another sheet, it'll add columns B-H for those rows. 

Is this difficult or what? Andrea 
:wave:


----------



## David M58 (May 20, 2008)

Andrea, take a look at the following code:


```
Sub rowsum()
    Dim sr1 As Long
    Dim sr2 As Long
    For sr1 = Sheets("Sheet1").Range("A2").CurrentRegion.Rows.Count To 2 Step -1
        For sr2 = 1 To Sheets("Sheet2").Range("A2").CurrentRegion.Rows.Count
            If Sheets("Sheet1").Cells(sr1, 1).Value = Sheets("Sheet2").Cells(sr2, 1).Value Then
                Sheets("Sheet1").Cells(sr1, 9).Formula = "=SUM(B" & sr1 & ":H" & sr1 & ")"
                Exit For
            End If
        Next sr2
    Next sr1
End Sub
```
The code goes through each value in column A on Sheet1 and looks for a match in column A on Sheet2. If the value is found on Sheet2, the code will insert a formula in column I in the same row as the matched value on Sheet1. The formula will sum the values in columns B-H. Modify as necessary.


----------

