Results 1 to 6 of 6

20081109, 22:17 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Macro needed to insert rows and delete duplicates (Excel 2003)
Hi,
I have tried and failed to find a formula to identify the appropriate rows to delete and where to add a new row. I planned to use the formula to identify and then run a macro to do the job of deleting rows and inserting rows.
Please assist me to automate the task with a macro.
Much appreciated.
PS: Instructions are in the attached workbook.Regards,
Rudi

20081109, 22:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Macro needed to insert rows and delete duplicates (Excel 2003)
What is the "Initiator CUK" number mentioned in your workbook?

20081109, 22:32 #3
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Macro needed to insert rows and delete duplicates (Excel 2003)
In trying to solve this probem, it seems that it must be done with arrays. It is easy to delete the induvidual GROUP numbers and even to find the duplicate GROUP and ID rows. But the problem is that it must NOT delete the rows of a complete GROUP if there is different ID's within them (even though some ID's within the GROUP are the same. This is where arrays need to be used; unless I am thinking too complex and an easier solution is overlooked.
TX again for the assistence.Regards,
Rudi

20081109, 22:33 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Macro needed to insert rows and delete duplicates (Excel 2003)
I thought I fixed that when I editted my post and resent the attachment. Sorry.
I was generalising the workbook and forgot to reword this to GROUP.Regards,
Rudi

20081109, 23:16 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Macro needed to insert rows and delete duplicates (Excel 2003)
The workbook must have resented it... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
Try this:
Option Explicit
Sub Test()
Dim r As Long
Dim m As Long
m = Cells(Rows.Count, 1).End(xlUp).Row
ReDim arr(2 To m) As Boolean
' Sort range
Range("A1:B" & m).Sort _
Key1:=Range("B1"), Key2:=Range("A1"), Header:=xlYes
' Fill array with yes/no values
For r = 2 To m
Range("C" & r).Formula = "=SUMPRODUCT(($A$2:$A$" & m & _
"=A" & r & ")*($B$2:$B$" & m & "=B" & r & "))"
Range("D" & r).Formula = "=COUNTIF($B$2:$B$" & m & ",B" & r & ")"
arr® = (Range("D" & r) = 1 Or Range("D" & r) = Range("C" & r))
Next r
' Delete extra cells
Range("C2" & m).ClearContents
' Delete rows
For r = m To 2 Step 1
If arr® Then
Rows®.Delete
End If
Next r
' New row count
m = Cells(Rows.Count, 1).End(xlUp).Row
' Insert rows
For r = m To 3 Step 1
If Not Range("B" & r) = Range("B" & (r  1)) Then
Rows®.Insert
End If
Next r
End Sub

20081109, 23:52 #6
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Macro needed to insert rows and delete duplicates (Excel 2003)
Hi Hans,
I notice there was an array involved. I have not used arrays in programming often and I got stuck when I encountered this. I had to change the references to your columns as the original data was in columns b and c. I also instructed the macro to insert two columns to add the formulas, and even added a screenupdating to freeze the screen. All in all, it worked well on my sample data and also on the original sheets after the changes were made.
TX a stack for the code.
CheersRegards,
Rudi