Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 15th, 2010, 08:52 AM
Registered User
 
Join Date: Jun 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mr.

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

Last edited by Shawki Hagagi; June 15th, 2010 at 08:57 AM..
 
Old June 15th, 2010, 10:20 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old June 16th, 2010, 03:59 AM
Registered User
 
Join Date: Jun 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mr.

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.
 
Old June 17th, 2010, 03:39 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
QUESTION FOR Mr. MINATEL miskovic BOOK: Professional Ajax 2nd Edition ISBN: 978-0-470-10949-6 4 March 11th, 2009 05:32 AM
Great Thanks Mr. Ivor yosri_negm BOOK: Ivor Horton's Beginning Visual C++ 2008 ISBN: 978-0-470-22590-5 0 September 4th, 2008 11:16 AM
To Mr. Thearon Willis luishrd BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 May 14th, 2006 02:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.