I think this will do it.
Sub removeNonMatches()
Dim bigRange As Range, storeArray As Variant
With ThisWorkbook.Sheets("mydata(5)")
.Range("B:B").Insert
With .Range("C:C")
Set bigRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
End With
With bigRange
storeArray = .Offset(0, -2).Value
With .Offset(0, -1)
.FormulaR1C1 = "=1/ISNUMBER(MATCH(RC[1],C1:C1,0))"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End With
.Offset(0, -2).Value = storeArray
.Parent.Range("B:B").Delete
End With
End Sub
See attached.
Bookmarks