Results 1 to 6 of 6
  1. #1
    Gold Lounger
    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.
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro needed to insert rows and delete duplicates (Excel 2003)

    What is the "Initiator CUK" number mentioned in your workbook?

  3. #3
    Gold Lounger
    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

  4. #4
    Gold Lounger
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  6. #6
    Gold Lounger
    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.

    Cheers
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •