Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Remove Duplicates Leaves the wrong data I need last row not first?

  1. #11
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,739
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Sorry Footoo, I just reads that back and it sounded a lot ruder then it did in my head, I didn't mean to sound ungreatful as your idea would be perfect normally.

    My document is old and has all sort of links and formulas and I worried if I Sort the data thensort it back it wont work properly as there are as fixed values that use things like "Indirect" so if I move them the Indirect is going to be looking at the wrong data,
    however I will test it and see if there is a way I could use it maybe making some change to the rest of the document.

    Thanks

    Tony

  2. #12
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    (P.S. I feel this is going off track! so please only reply if you have a solution or something I will find helpful as I have to keep restarting my PC)
    As I am not sure my solution is the solution you will like, I won't post it.

  3. #13
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,829
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    You could try this on a copy of your workbook. It also does use a helper column, but only temporarily. It does not use Remove Duplicates, but I think does what you want without re-ordering the data.

    Code:
    Sub LeaveLastDupe()
      Dim d As Object
      Dim a As Variant, b As Variant, aRws As Variant
      Dim rng As Range
      Dim lr As Long, lCalc As Long, nc As Long, i As Long, k As Long
      Dim s As String
      
      Set d = CreateObject("Scripting.Dictionary")
      d.comparemode = 1
      nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      With Columns("A:N")
        lr = .Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set rng = .Resize(lr)
      End With
      aRws = Evaluate("row(1:" & lr & ")")
      a = Application.Index(rng, aRws, Array(2, 9, 10))
      ReDim b(1 To lr, 1 To 1)
      For i = lr To 2 Step -1
        s = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3)
        If d.exists(s) Then
          b(i, 1) = 1
          k = k + 1
        Else
          d(s) = Empty
        End If
      Next i
      If k > 0 Then
        With Application
          .ScreenUpdating = False
          lCalc = .Calculation
          .Calculation = xlCalculationManual
        End With
        ActiveSheet.Unprotect
        Cells(1, nc).Resize(lr).Value = b
        Intersect(Cells(1, nc).Resize(lr).SpecialCells(xlConstants, xlNumbers).EntireRow, rng).Delete Shift:=xlUp
        Cells(1, nc).Resize(lr).ClearContents
        ActiveSheet.Protect
        With Application
          .ScreenUpdating = True
          .Calculation = lCalc
        End With
      End If
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com