# Clear duplicates between two columns

#### Pcwork2006

##### New Member
Good Days,

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!

Last edited:

#### My Aswer Is This

##### Well-known Member
Try this:
Code:
``````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

##### Active Member
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

Last edited:

#### My Aswer Is This

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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.

Code:
``````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

##### MrExcel MVP, Moderator
Another option
Code:
``````Sub Pcwork2006()
With Range("B1", Range("B" & Rows.Count).End(xlUp))
End With
End Sub``````

#### Jaafar Tribak

##### Well-known Member
Another option
Code:
``````Sub Pcwork2006()
With Range("B1", Range("B" & Rows.Count).End(xlUp))
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

##### MrExcel MVP, Moderator
The @ is just a place holder & is replaced by .Address by the Replace function, so it basically becomes
Code:
``Evaluate("if(" & .Address & "=" & .Offset(, -1).Address & ",""""," & .Address & ")")``

#### My Aswer Is This

##### Well-known Member
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