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!

 

[Jaafar Tribak]

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 & ")")

Thank you.

I was confused with the address thing .

If the the cell in column B is empty the result is 0 instead of blank.

How would you amend your code to result in empty cells instead of 0s ?

I used the Text function but it fails with cells in column B that contain numbers.

 

[Jaafar Tribak]

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

I agree with you but ,in my humble opinion, the important thing is to always learn new things whenever the opportunity arises.

Regards.

 

[Fluff]

Like

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

 

[Jaafar Tribak]

Like

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

Cool!

I'll experiment further with similar examples.

Thank you.

 

[Fluff]

You're welcome

 

[Pcwork2006]

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

Thank you,buddy.
This code solve my needs perfectly!

 

[Pcwork2006]

Like

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

Thank you for your answer.
However I got #value result in all Column B

 

[Fluff]

Not quite sure why you would get that & I cannot replicate it.
Have you made any changes to the code?

 

[Pcwork2006]

Not quite sure why you would get that & I cannot replicate it.
Have you made any changes to the code?

Sorry, you are correct.
Maybe I copied a wrong things.
Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback