I'd like to remove duplicates from a list using a pre-existing VBA code

Question

I'm looking to remove the duplicates from a list of numbers. Heres the code as it stands.

Dim combined As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(lr, 1), Cells(lr, 35)).Select
i = lr
While i > 1
    If i = lr Then
    combined = Cells(i, 1).Value
    Else
      combined = Cells(i, 1).Value & ";" & combined
    End If
i = i - 1
Wend
Cells(1, 2).Value = combined
Cells(1, 2).Select
Cells(1, 2).Copy
End Sub

Answer 1

You could use a Dictionary object.

Init a Dict to store unique values from the list then loop through each value in the list, checking if it already exists in the dictionary. If not add the va to the dict. Then combine the unique values into a semicolon-separated string and plop it to a cell.

  Sub RemoveDuplicates()
     Dim dict As Object
     Set dict = CreateObject("Scripting.Dictionary")
     
     Dim lr As Long
     Dim i As Long
     Dim value As Variant
     Dim combined As String
     
     lr = Cells(Rows.Count, 1).End(xlUp).Row
     
     ' Loop through the range and add unique values to the dictionary
     For i = 1 To lr
        value = Cells(i, 1).Value
        If Not dict.exists(value) Then
              dict(value) = True
        End If
     Next i
     
     ' Combine unique values into a string
     combined = Join(dict.keys, ";")
     
     ' Output the combined string to a cell
     Cells(1, 2).Value = combined
  End Sub