Previous Topic Next Topic

Excel - delete rows when alike

Solved/Closed
Amy - Feb 11, 2010 at 03:48 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 11, 2010 at 05:52 PM
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!
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 11, 2010 at 05:52 PM
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

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses