How do you automatically remove this data?

If you have data in columns A and B, how do you automatically remove the data in column A that exists in column B?

excel

Jamil

Feb 26, 2018

You can the below workbook in the Worksheet Object.

so whenever you enter new data in Column B and if any of the value in column B exists in Column A. it will automatically delete the duplicate from Column A.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False

Dim rowLoop As Long, foundRow As Long, test As String
  
  For rowLoop = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    test = Cells(rowLoop, "B")
    
    foundRow = 0
    
    On Error Resume Next
    foundRow = Columns("A").Find(test).Row
    On Error GoTo 0
    
    If Not foundRow = 0 Then
      Cells(foundRow, "A").Delete xlShiftUp
    End If
  Next rowLoop

    Application.EnableEvents = True
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:B")) Is Nothing Then Application.EnableEvents = False Dim rowLoop As Long, foundRow As Long, test As String For rowLoop = 1 To Cells(Rows.Count, "B").End(xlUp).Row test = Cells(rowLoop, "B") foundRow = 0 On Error Resume Next foundRow = Columns("A").Find(test).Row On Error GoTo 0 If Not foundRow = 0 Then Cells(foundRow, "A").Delete xlShiftUp End If Next rowLoop Application.EnableEvents = True End If End Sub

Forum Discussion

How do you automatically remove this data?