Excel - Delete rows with same value

June 2017




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


Published by aakai1056. Latest update on February 16, 2010 at 02:20 AM by aakai1056.
This document, titled "Excel - Delete rows with same value," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).