Clear duplicates between two columns

Pcwork2006

New Member
Joined
Jul 17, 2019
Messages
12
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!
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,302
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
Joined
Mar 16, 2019
Messages
261
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
Joined
Jul 5, 2014
Messages
16,302
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
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,497
Office Version
2016
Platform
Windows
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
Joined
Jun 12, 2014
Messages
32,402
Office Version
365
Platform
Windows
Another option
Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,497
Office Version
2016
Platform
Windows
Another option
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,402
Office Version
365
Platform
Windows
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
Joined
Jul 5, 2014
Messages
16,302
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
 

Forum statistics

Threads
1,082,510
Messages
5,365,993
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top