Consulting

Results 1 to 3 of 3

Thread: Delete First Duplicate Row

  1. #1
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location

    Delete First Duplicate Row

    I need some assistance modifying the below code to delete the first duplicate row and not the last duplicate row.



    Sub DeleteDups2()
    
        Dim x As Long
        Dim LastRow As Long
        Dim ws As Worksheet
        Dim rngToDel As Range
        'change sheet1 to suit
        Set ws = ThisWorkbook.Worksheets("Data")
    
    
        With ws
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For x = LastRow To 2 Step -1
                If Evaluate("=ISNUMBER(MATCH('" & .Name & "'!A" & x & " & '" & .Name & "'!B" & x & ",'" & .Name & "'!A1:A" & x - 1 & " & '" & .Name & "'!B1:B" & x - 1 & ",0))") Then
                    If rngToDel Is Nothing Then
                        Set rngToDel = .Range("A" & x)
                    Else
                        Set rngToDel = Union(rngToDel, .Range("A" & x))
                    End If
                End If
            Next x
        End With
    
    
        If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
    End Sub
    Thanks!

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    try:
    Sub DeleteDups3()
    
    Dim x As Long
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim rngToDel As Range
    'change sheet1 to suit
    Set ws = ThisWorkbook.Worksheets("Data")
    
    
    With ws
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For x = 1 To LastRow - 1
         If Evaluate("=ISNUMBER(MATCH('" & .Name & "'!A" & x & " & '" & .Name & "'!B" & x & ",'" & .Name & "'!A" & x + 1 & ":A" & LastRow & " & '" & .Name & "'!B" & x + 1 & ":B" & LastRow & ",0))") Then
          If rngToDel Is Nothing Then
            Set rngToDel = .Range("A" & x)
          Else
            Set rngToDel = Union(rngToDel, .Range("A" & x))
          End If
        End If
      Next x
    End With
    
    
    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location
    Thanks worked perfectly.

Posting Permissions

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