# Excel - Delete rows with same value

October 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

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.