Option Explicit
Sub ClearItemInColumnCWhenColumnAHasDuplicatesInB()
'140203
'Erik Van Geit
'will clear the contents of column C when column A has duplicates anywhere in column B
'CAVEAT
'If the sheet could already have an autofilter, this code must get some additional lines.
'START WITH
' A B C
'1 Header A Header B Header C
'2 G item 1
'3 A item 2
'4 B item 3
'5 F B item 4
'6 F item 5
'7 E A item 6
'8 D item 7
'9 A D item 8
'10 G item 9
'11 H item 10
'12 G item 11
'TO GET
' A B C
'1 Header A Header B Header C
'2 G item 1
'3 A
'4 B
'5 F B item 4
'6 F item 5
'7 E A item 6
'8 D
'9 A D
'10 G item 9
'11 H item 10
'12 G item 11
Dim LRColA As Long
Dim LRColB As Long
Dim LC As Long
With Sheets("Sheet1")
LRColA = .Cells(Rows.Count, "A").End(xlUp).Row
LRColB = .Cells(Rows.Count, "B").End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
With .Range(.Cells(1, LC + 1), .Cells(LRColA, LC + 1))
'could leave out the IF( ... ,REMOVE KEEP) and filter using TRUE
.FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC1,R1C2:R" & LRColB & "C2,0)),""REMOVE"",""KEEP"")"
.AutoFilter Field:=1, Criteria1:="REMOVE"
End With
.Range("C2:C" & Rows.Count).ClearContents
.Columns(LC + 1).Delete
End With
End Sub