Remove duplicate rows

[mlbarry]

I have two spread sheets that I combine. One is openinvoices and the other is payments, so I have a spread sheet with all open invoicesand payments for invoices that were paid that day. Is there a way to remove theinvoices that were paid without having to go through and select every line anddelete the rows manually? So I have column B (invoice #) and column D (invoice/payment$) if these two match exactly I want these rows removed, there may be severhundred that meet this requirement on this spread sheet.

Customer Name	Invoice #	Date	Amount
AAA	1001	5/1/13	$500.00
	1001		$500.00
BBB	1002	6/1/13	$450.00
CCC	1003	7/1/13	$250.00
DDD	1004	6/15/13	$100.00

<tbody>
</tbody>

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = LR To 2 Step -1
  If Cells(r, 2) = Cells(r - 1, 2) And Cells(r, 4) = Cells(r - 1, 4) Then
    Rows(r).Delete
  End If
Next
End Sub

 

[mlbarry]

I had to change the column numbers (below) because the accrual spread sheet is Invoice # (Column D) and Invoiced / Paid (Column K) but it only removed one of the rows (it removed the highlighted yellow only) and I need to remove both the invoice and payment only if they equal. In the below example I would need to remove row 3 & 4.

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = LR To 4 Step -1
If Cells(r, 4) = Cells(r - 1, 4) And Cells(r, 11) = Cells(r - 1, 11) Then
Rows(r).Delete
End If
Next
End Sub

	A	B	C	D	E	F	G	H	I	J	K
1	Customer	PO	Name	Invoice #	xxxx	Invoice Date	xxxx	xxxx	xxxx	xxxx	Invoiced / Paid
2	DDD	500	DDD	1004		3/3/13					$900.00
3	AAA	200	AAA	1001		1/1/13					$300.00
4				1001							$300.00
5	BBB	300	BBB	1002		2/1/13					$600.00
6	CCC	400	CCC	1003		3/1/13					$900.00

<tbody>
</tbody>

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = LR To 2 Step -1
  If Cells(r, 4) = Cells(r - 1, 4) And Cells(r, 11) = Cells(r - 1, 11) Then
    Rows(r).Delete
    Rows(r-1).Delete
   End If
Next
End Sub

 

[mlbarry]

Perfect, thanks

 

[mlbarry]

One more thing. We also have discounts taken, in the example above discounts would be in column J, so if J4 + K4 = the total in K3 and D3 and D4 equal then delete both rows 3 and 4

 

[patel45]

Sub a() 
LR = Cells(Rows.Count, "A").End(xlUp).Row
 For r = LR To 2 Step -1
   If Cells(r, 4) = Cells(r - 1, 4) And Cells(r, 11) = Cells(r - 1, 11) Then     
     Rows(r).Delete
     Rows(r-1).Delete 
   End If
   If Cells(r, 10) + Cells(r, 10) = Cells(r - 1, 11) And Cells(r, 4) = Cells(r - 1, 4) Then     
     Rows(r).Delete
     Rows(r-1).Delete 
   End If
 Next
 End Sub

 

[mlbarry]

I get an error message on:
If Cells(r, 10) + Cells(r, 10) = Cells(r - 1, 11) And Cells(r, 4) = Cells(r - 1, 4) Then


I've run the first part of the macro and it takes a very long time, about 1.5 hours. I have over 4000 line items in a table. Do you have any sujestions?