Excel - Delete rows with same value

December 2016




Issue


I am in need of a formula to solve this problem. I want it to find similiar cells and delete those cells from the worksheet.

Here is a sample:
A B C D  
Transaction Date Transaction Reference Description Amount  
1/25/2010 STD0182 13993 (108.16)  
1/25/2010 STD0182 - Glenn D Smith 13933 - Q1 2010 773 99.00  
1/25/2010 STD0182 - Glenn D Smith 13933 - Q1 2010 Tax 773 9.16  
1/25/2010 STD0268 1974 (423.72)  
1/25/2010 STD0268 - Adv Advisory Service 1974 - Q1-Q4 2010 773 396.00  
1/25/2010 STD0268 - Adv Advisory Service 1974 - Q1-Q4 2010 Tax 773 27.72  
1/25/2010 STD0431 7206 (108.16)  
1/25/2010 STD0431 - Just Plans Etc 7206 - Q1 2010 773 99.00  
1/25/2010 STD0431 - Just Plans Etc 7206 - Q1 2010 Tax 773 9.16  
1/25/2010 STD0690 16670 (107.66)  
1/25/2010 STD0690 - Asset Planning Sol 16670 - Q1 2010 773 99.00  
1/25/2010 STD0690 - Asset Planning Sol 16670 - Q1 2010 Tax 773 8.66 


So I want the formula to look up either the similar STD#'s (STD0182 - Transaction Ref) and to delete if they contain the same STD#.

Solution


Not entirely clear about this part "STD0182 - Transaction Ref"

Assumptions.
1. You want to delete a row if, its column B value is same as the value in the row above it
2. Data is sorted on column B
3. Column B Does not contain blank cell

Sub removeDups()  

Dim myRow As Long  
Dim sTRef As String  


sTRef = Cells(2, 2)  

myRow = 3  
Do While (Cells(myRow, 2) <> "")  

If (sTRef <> Cells(myRow, 2)) Then  
sTRef = Cells(myRow, 2)  
myRow = myRow + 1  

Else  

Rows(myRow).Select  
Selection.Delete Shift:=xlUp  

End If  

Loop  

End Sub

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Delete rows with same value » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.