Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Location
    Cincinnati
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Doubles in an Excel Sheet

    I have a copy of an old list and a new list. I've got vba script combining them into one list, sorting them by name, and assigning a function down the column near this list. My goal is to identify cells with the same value as another (there will be a maximum of two of each value) with one character and to delete all rows with that character, so I can see what changed between each list.

    I currently put =MATCH(B2, B:B, 0) in the D column and copy it for as far as the combined list goes, but that gives me another list of doubles (the first cell the value is found in). What function would assign a character when it found a double?

    The relative part of my vba script:
    'Match Up the New and Old
    last_row = Application.WorksheetFunction.CountA(Range("B:B"))
    ActiveSheet.Range("D2").Value = "=IFERROR(MATCH(B2, B:B, 0),-1)"
    ActiveSheet.Range("D2").Copy
    ActiveSheet.Range("D3" & last_row).Select
    ActiveSheet.Paste
    ActiveSheet.Range("D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'Delete the matches
    'Combine the remaining lists

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("D2" & last_row), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
    .SetRange Range("A1" & last_row)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
    End With

  2. #2
    New Lounger
    Join Date
    Jul 2011
    Location
    Cincinnati
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's the part that is supposed to delete the rows:

    Set cc = ActiveSheet.Range("D").Find(-1, LookIn:=xlValues, SearchDirection:=xlPrevious)
    If Not (cc Is Nothing) Then
    ActiveSheet.Rows("2:" & cc.Row).Delete Shift:=xlUp
    End If
    ActiveSheet.Columns("D").ClearContents

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Do you know there is a 'Remove Duplicates' button in the Data tab of the ribbon? This doesn't require the cells to be sorted into adjacent cells. Perhaps you could make use of that method with your macro instead.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    New Lounger
    Join Date
    Jul 2011
    Location
    Cincinnati
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, I did not. Thank you for the tip!

Posting Permissions

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