Excel - delete rows when alike [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

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#.

Is this possible?

Thanks!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!