Clear duplicates between two columns

[Pcwork2006]

Good Days,
Please advise a vba code to process the duplicates values clean task,

If word/text in Cell A1 is equal to Cell B2 ,then Cell B2.clearcontents

......

If word/text in Cell A65536 is equal to Cell B65536, then Cell B65536.clearcontents

Else, no change

Thank you very much!

 

[My Aswer Is This]

Try this:

Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub

 

[Vishaal]

Hi,

how can we delete entire row instead of copying value,

also can we compare Col 1 & Coloumn 2
Coloumn 2 & Coloumn 3 also with this formula

Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

This is not your question. This question was asked by the original poster. If you have a question it's best to start a new posting.

Hi,

how can we delete entire row instead of copying value,

also can we compare Col 1 & Coloumn 2
Coloumn 2 & Coloumn 3 also with this formula

Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub

 

[Vishaal]

This is not your question. This question was asked by the original poster. If you have a question it's best to start a new posting.

ok

 

[Jaafar Tribak]

Probably faster without using a loop :

Uses column CX (COL# 102) as temporary helper column ... If the worksheet is protected then the code should be slightly amended as required.

Option Explicit

Sub Test()

    Dim oRange As Range
    Dim lCalculationMode As Long
    
    On Error GoTo errHandler
    
    lCalculationMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Set oRange = Columns("A:B").RowDifferences(Range("A1"))
    oRange.Offset(, 100).Value = "~#@" [B][COLOR=#008000] '<=== Use column CX as a temporary help column[/COLOR][/B]
    Set oRange = oRange.Offset(, 100).EntireColumn.ColumnDifferences(oRange.Offset(, 100).Cells(1).End(xlDown))
    oRange.Offset(, -100).ClearContents
    oRange.EntireColumn.ClearContents
    
errHandler:
    Application.Calculation = lCalculationMode
    Application.EnableEvents = True

End Sub

 

[Fluff]

Another option

Sub Pcwork2006()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@=" & .Offset(, -1).Address & ","""",@)", "@", .Address))
   End With
End Sub

 

[Jaafar Tribak]

Another option

Sub Pcwork2006()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@=" & .Offset(, -1).Address & ","""",@)", "@", .Address))
   End With
End Sub

I like it but I am having a hard time trying to break down the "if(@... .Address) part so I can understand how this works.

Can you explain it for me ?

Thanks.

 

[Fluff]

The @ is just a place holder & is replaced by .Address by the Replace function, so it basically becomes

Evaluate("if(" & .Address & "=" & .Offset(, -1).Address & ",""""," & .Address & ")")

 

[My Aswer Is This]

I like using simple Vba coding which I understand and hopefully others will to. But I'm sure other ways of coding things may be a few seconds faster.
My code worked for me which was in post 2