Delete duplicates from a column and not have it affect the rest of the row

Question

This code is deleting the whole row when eliminating the dup. I want to delete the dup but keep data in the rest of the row in other columns.

Dim rg As Range
Set rg = Range("CJ1")
rg.RemoveDuplicates Columns:=88, Header:=x1yes

Answer 1

• Load data into an array, clear the dup. and write output to sheet

Option Explicit

Sub RemoveDup()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "CJ").End(xlUp).Row
    ' load data into an array
    Dim rngData As Range: Set rngData = ws.Range("CJ1:CJ" & lastRow)
    Dim arrData: arrData = rngData.Value
    Dim i As Long
    Dim objDic As Object, sKey As String
    Set objDic = CreateObject("scripting.dictionary")
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1)
        If objDic.exists(sKey) Then
            arrData(i, 1) = ""
        Else
            objDic(sKey) = ""
        End If
    Next
    rngData.Value = arrData
End Sub