Option Explicit
Sub test()
Dim arr, dic, i, j, m
Set dic = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion.Resize(, 6)
For i = 2 To UBound(arr, 1)
For j = 2 To UBound(arr, 1)
If Len(arr(i, 3)) Then
If arr(i, 3) = arr(j, 4) Then dic(i) = 1: dic(j) = 1: Exit For
End If
Next
Next
m = 1
For i = 2 To UBound(arr, 1)
If dic.exists(i) = False Then
m = m + 1
For j = 1 To UBound(arr, 2)
arr(m, j) = arr(i, j)
Next
End If
Next
With [a1]
.Resize(UBound(arr, 1) * 2, UBound(arr, 2)).ClearContents
.Resize(m, UBound(arr, 2)) = arr
End With
End Sub |