none
Remove from sheet ALL identical rows based on N columns (not just duplicates) RRS feed

  • Question

  • In excel sheet, all rows with the same data in A:A and E:E must be removed:

     A       B      C       D      E

    120   ***   ***   ***   654

    333   ***   ***   ***   999

    023   ***   ***   ***   555       

    120   ***   ***   ***   654

    555   ***   ***   ***   888 

    120   ***   ***   ***   654

    333   ***   ***   ***   999

      

    For above example, rows 1,4,6 and 2,7 must be removed. 

    How to make that with nice VBA code? (I made that with macro recorded, but code is ugly and work slowly.)

     



    • Edited by denvic999 Thursday, August 15, 2019 4:31 PM
    Thursday, August 15, 2019 4:06 PM

All replies

  • Try something like this:

     

        Dim ws As Worksheet: Set ws = MyWorksheet1
        
        Dim rA As Range: Set rA = Intersect(ws.Columns("A"), ws.UsedRange)
        Dim rE As Range: Set rE = Intersect(ws.Columns("E"), ws.UsedRange)
     
        Dim vA: vA = rA.Value
        Dim vE: vE = rE.Value
        
        Debug.Assert rA.Rows.Count = rE.Rows.Count
        Debug.Assert LBound(vA, 1) = 1
        Debug.Assert LBound(vE, 1) = 1
        Debug.Assert LBound(vA, 2) = 1
        Debug.Assert LBound(vE, 2) = 1
        Debug.Assert UBound(vA, 1) = UBound(vE, 1)
        Debug.Assert UBound(vA, 2) = 1
        Debug.Assert UBound(vE, 2) = 1
        
        Dim b() As Boolean: ReDim b(1 To UBound(vA, 1))
        
        Dim i As Integer, j As Integer
        
        For i = UBound(vA, 1) To 2 Step -1
            For j = i - 1 To 1 Step -1
                If vA(j, 1) = vA(i, 1) Or vE(j, 1) = vE(i, 1) Then
                    b(i) = True
                    b(j) = True
                End If
            Next j
        Next i
     
        For i = UBound(b) To 1 Step -1
            If b(i) Then
                ws.Rows(rA.Row + i - 1).EntireRow.Delete
            End If
        Next i

    Replace MyWorksheet1 with your worksheet.



    • Edited by Viorel_MVP Thursday, August 15, 2019 6:04 PM
    Thursday, August 15, 2019 6:03 PM
  • Thanks for reply.  Trying to apply, and on compiling:

    rE.Value = <Object variable or With block variable not set> for

    Dim vE: vE = rE.Value

    Also, I tested suggested code with data placed in personal book itself, and it works

    improperly.

    • Edited by denvic999 Sunday, August 18, 2019 4:09 AM
    Thursday, August 15, 2019 8:07 PM