Erase duplicated value within row of uncertain size

Question

I want to write a code that will erase the duplicated value within a row of uncertain size, i.e. I don't know where the duplicated value will apear.

I thought that I may be able to use RemoveDuplicates property within a range but it only works for duplicates within column. That's why I am stacked.

This is the case before running the code:

And this is my desired outcome:

I really wish to have a code that will use resizable range with rows.count as I said, I don't know where the duplicate can appear and the row can be very long (up to 500 records).

This is what tried but obviously, I cannot use as there is no Remove duplicate property within row:

Sub RemoveDuplicates()
Dim ws1 As Worksheet

Set ws1 = Sheets("Sheet1")

Dim rng As Range

Dim LastCol As Integer

 With ws1

    LastCol = .Cells(2, Columns.Count).End(xlToLeft).Column

    Set rng = .Range(.Cells(2, 1), .Cells(2, LastCol))

    rng.RemoveDuplicates ????

End With

I would appreciate any help.

I have no clue, but the interface of my profile is like 90s too. — Dozens, Mar 31 '19 at 15:04
Use a `Dictionary` to store the values, and if `Dict.Exists` use `.ClearContents` to cleat that cell — Shai Rado, Mar 31 '19 at 15:21
But to use dictionary, I would need to know upfront what is the duplicated value, and I don't know which one that would be. — Dozens, Mar 31 '19 at 15:45
@Dozens, you can build you dictionary object as you go, all you need to do is to check whether the value in current cell was seen before, and clear the cell if it's present in the dict — Saharsh, Mar 31 '19 at 15:56

score 2 · Answer 1 · answered Mar 31 '19 at 15:45

You could do it like that

Sub RemoveDuplicates()
    Dim ws1 As Worksheet
    Set ws1 = Sheets("Sheet1")

    Dim rng As Range   
    Dim LastCol As Integer

    With ws1
        LastCol = .Cells(2, Columns.Count).End(xlToLeft).Column    
        Set rng = .Range(.Cells(2, 1), .Cells(2, LastCol))
        'rng.RemoveDuplicates ????
    End With

    Dim v As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    v = rng
    Dim i As Long
    For i = LBound(v, 2) To UBound(v, 2)
        If dict.Exists(v(1, i)) Then
            v(1, i) = vbNullString
        Else
            dict.Add v(1, i), v(1, i)
        End If
    Next i
    rng = v
End Sub

It is really good and I appreciate your post because I could learn something new with scripting reference. However, I need to have this code in the worksheet without those references added (Unfortunately). But your suggestion is really great — Dozens, Mar 31 '19 at 17:49

Saharsh · Answer 2 · 2019-03-31T15:53:33.917

A SET data structure is more appropriate for this kind of operation, but Excel provides Dictionary, and as Shai Radio mentioned in the comments, it could be used here. Refer this to reference dictionary in your project Does VBA have Dictionary Structure?

Your code can then be modified to the following:

Sub RemoveDuplicates()
    Dim ws1 As Worksheet
    Set ws1 = Sheets("Sheet1")
    Dim rng As Range
    Dim dict As New Scripting.Dictionary
    Dim LastCol As Integer
    With ws1
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 1 To LastCol
            If Not dict.Exists(.Cells(1, i).Value) Then
                dict.Add .Cells(1, i).Value, 1
            Else
                .Cells(1, i).ClearContents
            End If
        Next i
    End With
End Sub

Thank you for the suggestion and post, I will play around with in the future. Sadly, I need to get this work done in workbook without scripting reference :/ — Dozens, Mar 31 '19 at 17:54

score 0 · Accepted Answer · answered Mar 31 '19 at 15:49

if you want to use RemoveDuplicates() feature in a row-like range, you can use a "helper" column-like range to put your data into, RemoveDuplicates and paste the result back to your original range

Sub RemoveDuplicates()
    Dim ws1 As Worksheet        
    Set ws1 = Sheets("Sheet1")

    Dim dataRng As Range, helpRng As Range

    With ws1        
        Set dataRng = .Range("A2", .Cells(2, Columns.Count).End(xlToLeft)) ' this is your original data range

        With .UsedRange
            Set helpRng = .Cells(1, .Columns.Count + 1).Resize(dataRng.Columns.Count) ' ' this is "out of town" helper range, with as many rows as your data range columns
        End With

        With helpRng
            .Value = Application.Transpose(dataRng.Value)
            .RemoveDuplicates Columns:=Array(1), Header:=xlNo
            dataRng.Value = Application.Transpose(.Value)
            .Clear
        End With
    End With
End Sub

If I would like to apply the same code but instead having strings, I have the dates. How could I modify this code? Because it does not work in case of dates. Or should I ask it in another post ? — Dozens, Apr 02 '19 at 07:25

Erase duplicated value within row of uncertain size

3 Answers3