vba - Delete duplicate entries in a given row

Question

I want to delete the duplicates in each row, such that there should be no "holes" in the row. What I have is:

Col A    Col B   Col C    Col D    Col E   Col F   Col G
A         B        C        D        A       B       A
J         I        K        J        I       K       I
B         A        B        J        I       K       L

up to 40k rows.
Output required:

Col A    Col B   Col C    Col D    Col E   Col F   Col G
A         B        C        D       
J         I        K        
B         A        J        I       K       L

Answer 1

I suggest iterating over each row in the range, extracting the values, generating the unique set, and repaste into the row.

The following function takes an array of values and returns the unique values in the array, using a Scripting.Dictionary. Add a reference (Tools -> References...) to the Microsoft Scripting Runtime.

Function Unique(values As Variant) As Variant()
    'Put all the values as keys into a dictionary
    Dim dict As New Scripting.Dictionary, val As Variant
    For Each val In values
        dict(val) = 1
    Next
    Unique = dict.Keys
End Function

Then you can do the following:

Dim rng As Range, row As Range
Set rng = ActiveSheet.UsedRange
For Each row In rng.Rows
    Dim values() As Variant 'We need this to extract the values from the range, and to avoid passing in the range itself
    values = row
    Dim newValues() As Variant
    newValues = Unique(values)
    ReDim Preserve newValues(UBound(values, 2)) 'without this, the array will be smaller than the row, and Excel will fill the unmatched cells with #N/A
    row = newValues
Next