How can I remove duplicate rows and reversed-order duplicate rows in VBA-Excel

Question

-updated -

I wonder how to remove duplicate rows based on values in two columns, where duplicates can occur in reversed order too. I want to remove whole rows, not only duplicate cells in columns. I have searched the forum but cant find the exact answer to this.

If the data looks like:

AA
AB
BA
CA
AC

I want to keep:

AA
AB
CA

So far I have only seen code to remove one-way duplicates (e.g. Delete all duplicate rows Excel vba ), which would not remove any rows from the example above.

I will need to calculate the distance between two named points, so what row I keep does not matter. Maybe I should also specify that the format is e.g. A1 and A2 (letter + number) in each cell, thus A1+A2 and A2+A1 would be a duplicate. Not sure if this matters.

Does anyone know or can hint me in the right direction?

Thanks Lisarv

Answer 1

Since you already have a solution based on a single column, we will find a way to apply that solution. With data in columns A and B, in C1 enter:

=IF(A1<B1,A1 & CHAR(1) & B1,B1 & CHAR(1) & A1)

and copy down. Then apply your solution to column C:

Note:

We use CHAR(1) to prevent accidental matches.

EDIT#1

Here is a pure VBA solution without using column C:

Sub RowKiller()
   Dim N As Long, C() As String, i As Long
   Dim aa As String, bb As String, cc As String
   Dim s As String
   s = Chr(1)
   N = Cells(Rows.Count, "A").End(xlUp).Row
   ReDim C(1 To N)

   For i = 1 To N
      aa = Cells(i, 1).Value
      bb = Cells(i, 2).Value
      If aa < bb Then
         C(i) = aa & s & bb
      Else
         C(i) = bb & s & aa
      End If
   Next i

   For i = N To 2 Step -1
      cc = C(i)
      For j = i - 1 To 1 Step -1
         If cc = C(j) Then Rows(i).Delete
      Next j
   Next i
End Sub