Mr.

Shawki Hagagi · June 15th, 2010, 08:52 AM

Please can somebody help.
I have an excel sheet that conatins the following:
A, B, C, D, E, F
Seq, Ref. , Source, Debit, Credit, Balance
8, 111, 2, 5, 0, 123467
2, 120, 1, 10, 0, 123466
2, 120, 2, 10, 0, 123466
4, 6666, 1, 50, 0, 123496
9, 6666, 2, 50, 0, 123517
7, 8787, 2, 50, 0, 123462
5, 485, 1, 50, 0, 123546
10, 485, 2, 50, 0, 123567
11, 22, 2, 50, 0, 123617
12, 125544, 2, 12354, 0, 135971
7, 777, 1, 0, 7, 123519
4, 777, 2, 0, 7, 123439
5, 888, 2, 0, 7, 123432
3, 505, 1, 0, 20, 123446
6, 505, 2, 0, 20, 123412
3, 951, 2, 0, 20, 123446
6, 546546, 1, 0, 20, 123526
8, 1113, 1, 0, 55684, 67835
13, 1113, 2, 0, 55684, 80287

I want to write VBA code that would enable me to read th first row and match it against the remaining rows in the range to find a match. A match for the first row is a row that has (5) in Debit, (111) in Ref. No. but a (1) in Source. In the above example there is no match to first row, so it stays. When we move on to the second row, there is match because row three has the same debit and ref. no. and a 2 in source. Because there is a match, the program should delete both row 2 and row 3. It should then move on to the subsequent row. In the end the result should be as follows:

8, 111, 2, 5, 0, 123467
7, 8787, 2, 50, 0, 123462
11, 22, 2, 50, 0, 123617
12, 125544, 2, 12354, 0, 135971
5, 888, 2, 0, 7, 123432
3, 951, 2, 0, 20, 123446
6, 546546, 1, 0, 20, 123526

The above are rows that occure once only with no match.
Many thanks

Shasur · June 15th, 2010, 10:20 PM

Hi

I think you wanted to remove duplicates. Can you check if the following can be tweaked for your requirement

http://vbadud.blogspot.com/2007/08/d...excel-vba.html

http://vbadud.blogspot.com/2008/07/r...eet-using.html

Cheers
Shasur

Shawki Hagagi · June 16th, 2010, 03:59 AM

Dear Shasur
Thank you for your reply.

I have recreated the sheet and the VBA code in the link you provided.

(http://vbadud.blogspot.com/2008/07/r...eet-using.html)

Found that it contained 2 syntex errors:

oRange.RemoveDuplicates(Columns:=2)......changed to:
oRange.RemoveDuplicates (Columns = 2)

and
MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples") ............ changed to

MsgBox (Err.Number & " - " & Err.Description)

when I ran the code I got 2 errors. These are:
19 object variable or With block variable not set (twice)
7 Out of memory

Even though, this is not exactly what I want. What I need to do is to remove all duplicate records in other words the actual record and its duplicate. so if we take the example in the link you provided, the result shown is not what I want.
My result should show only 2 records namely
Banana
Grapes
both Apple records removed
both Orange records removed and
both Pear records removed.

English is my second language, so I hope I have made myself understood now.
Many thanks for your help.

Shasur · June 17th, 2010, 03:39 AM

Hi

Please check if the following code helps you remove duplicates:

Code:

Sub Remove_Dupe_Row_Data()
Dim iMax As Long            ' Long M
Dim oWS As Worksheet            ' Sheet
Dim oFnd As Range
Dim sText As String
Dim i1 As Long

Set oWS = ActiveSheet
iMax = oWS.Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = 2 To iMax
    sText = oWS.Cells(i1, 4).Value
    Set oFnd = oWS.Range(oWS.Cells(1, 4), oWS.Cells(i1 - 1, 4)).Find(sText)
    If Not oFnd Is Nothing Then
        oWS.Rows(i1).EntireRow.Delete ' Delete the entire row
        
    End If
Next i1
    

End Sub

It deletes the duplicate rows (I have taken Column D as a base). Check if it works for you

Cheers
Shasur