# Remove from sheet ALL identical rows based on N columns (not just duplicates)

• ### 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 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```

`Dim vE: vE = rE.ValueAlso, I tested suggested code with data placed in personal book itself, and it works improperly.`