VBA deleting duplicate values from 2 columns

Question

I have a list with 3 columns

for example

I want to delete any duplicate value with no shifting, the duplicated values can be both on the first column and on the second.

How can I do that?

I've tried something but it didn't work

Sub RemoveDuplicates()
Dim rng As Range
Dim x As Long
Dim lRow As Long
Dim i As Integer

Columns("B:C").Select
    Range("C1").Activate
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="0", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
i = 1
x = 1
Do While Cells(i, 1).Value <> ""
    Cells(i, 4) = "=CONCATENATE(0,RC[-2])"
    i = i + 1
Loop
Do While Cells(x, 1).Value <> ""
    Cells(x, 5) = "=CONCATENATE(0,RC[-2])"
    x = x + 1
Loop
    Columns("D:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Columns("D:E").ClearContents
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With ThisWorkbook.Sheets(1)
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng = ThisWorkbook.Sheets(1).Range("B2:C" & lRow)
End With

For x = rng.Cells.Count To 1 Step -1
    If WorksheetFunction.CountIf(rng, rng(x)) > 1 Then
        rng(x).ClearContents
    End If
Next x

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

The first thing coming to mind is using a loop to go through each row of each column, match() the value in the activecell, and if it matches, delete and move to next cell in the range. — Cyril
– Cyril, Commented Jan 19, 2017 at 13:20
you can add the Macro to the Toolbar excel-easy.com/vba/examples/add-a-macro-to-the-toolbar.html — Slai
– Slai, Commented Jan 24, 2017 at 22:28

Jordan · Accepted Answer · 2017-01-24 10:13:53Z

1

Try this where your two columns are B and C. It loops through all of the data and uses the worksheet function COUNTIF to check if there is more than one occurrence of each value and clears the contents of the cell if there is a count of more than 1:

Sub RemoveDuplicates()

Dim rng As Range
Dim x as Long
Dim lRow as Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With Thisworkbook.Sheets("SheetName")
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng = .Range("B2:C" & lRow)
End With

For x = rng.Cells.Count To 1 Step -1
    If WorksheetFunction.CountIf(rng, rng(x)) > 1 Then
        rng(x).ClearContents
    End If
Next x

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

edited Jan 24, 2017 at 10:13

answered Jan 19, 2017 at 13:27

Jordan

4,5342 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bakman Over a year ago

thnx. its works great. but i have a problem, ive made an actives bottun with the command ant it works, but when i trie to make the same code as a macro command it didnt work and the debuger highlighted the row "With ThisWorkbook.Sheets("Sheet1")". the name of the seet is "Sheet1" so i dont understand whats the prob

Jordan Over a year ago

That's odd, are you sure there aren't any leading/trailing spaces in the name, e.g. " Sheet1" or "Sheet1 "? I'm not sure what else the issue could be - you could always just use Thisworkbook.Sheets(1) if the worksheet is the first one in the workbook.

bakman Over a year ago

ive uploaded the whole code, maybe the problem is elsewhere

Slai Over a year ago

@bakman you might be able to use the CodeName instead With Sheet1 or maybe fully qualify it Application.Thisworkbook.Worksheets("Sheet1")

bakman Over a year ago

nothing works, when i use the code in an activex button it does works, so is there a way to add this button to excel so it will apper wen ever i open an excel file?

Collectives™ on Stack Overflow

VBA deleting duplicate values from 2 columns

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related