Compare two lists and remove the common items

Suppose you end up in a situation that you have a list in column A and another list in column B which is a super set of list A, that is it contains all elements of A and plus there are other ones too.
Now, you wish to delete these elements in list A from those in list B then use this simple macro:
What this macro does is that it compares list A with list B and deletes all the values in A from B, hence giving you a list in B that is free from elements in list A.
with a little bit of playing around you can tweak the list locations to be on different sheets or files or columns as you wish.
Copy the code below in red and paste it to create a new macro in the developer's tab in your excel.


Option Explicit

Sub DupeRemoval()
'JBeaucaire  (10/7/2009)
Dim LR As Long, LC As Long
LC = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 5
LR = Range("B" & Rows.Count).End(xlUp).Row

Cells(1, LC) = "Key"
Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,C1,0))"
Cells(1, LC).AutoFilter
Cells(1, LC).AutoFilter Field:=1, Criteria1:="True"
Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).ClearContents
Cells(1, LC).AutoFilter
Columns(LC).ClearContents
Range("B2:B" & LR).Sort [B1], xlAscending
MsgBox LR - Range("B" & Rows.Count).End(xlUp).Row & " items were deleted"
End Sub

Post a comment

0 Comments