Remove duplicates from 1 dimensional array in VBA

Is there a quick way to remove duplicate entries from a 1 dimensional array?

Say I have an array of 1,000 entries and elements 150 and 200 are already listed elsewhere in the array. How would I remove these two lines and return the array with a new size of 998 elements?

Finding the dups appears straightforward enough, simply sort into order and work through the array checking the lines with the previous one. It's the removing and resizing (or redimming) that's got me stumped.

Thanks

You should be able to modify this code which populates an array from a named range ( one column wide) "input", extracts the unique values and writes them a range named "output".

Sub UniqueList()
Dim i As Long
Dim arr As Variant
Dim Unique As New Collection

arr = Sheet1.Range("input")

For i = 1 To UBound(arr, 1)
    On Error Resume Next
    cUnique.Add arr(i, 1), CStr(arr(i, 1))
Next row

ReDim arr(1 To Unique.Count, 1 To 1)
For i = 1 To Unique.Count
    arr(i, 1) = cUnique(i)
Next row
Sheet1.Range("output").Resize(UBound(arr, 1), 1).Value = arr

End Sub

Originally Posted by zaphodb2003

Finding the dups appears straightforward enough, simply sort into order and work through the array checking the lines with the previous one. It's the removing and resizing (or redimming) that's got me stumped.

After sorting, go through Array1 and copy the non-duplicates to a second array (Array2).
Array2 is dynamic and is dimensioned to the same size as Array1.
Each time a duplicate is found reduce the size of Array2 by 1.
Optional - when finished resize Array1 and assign Array2 to Array1.

Yes you could take that approach and use two arrays .
The code I posted uses a collection to filter out duplicates.
Using a collection will tend to simplify your code.

Yes, a collection would be a better choice to prevent duplicate values from being stored.

What's happened to the OP? No response.

We'll just talk among ourselves.

I used the dictionary to remove dupplicates.
I have a list of students who failed and I wanted a list with all the subjects, as many students failed the same subjects I had to remove dupplicates. I worked from the worksheet without using an array, but it is easy to adapt this code by writing the info in an array first and after send the array to the function.
It's a code snippet I don't use regularly, so I didn't optimise it, my excuses , it's a draft "as it is"

Sub Resit()
Dim nInt_i As Integer
Dim nInt_j As Integer
Dim rngStart As Range
    Set rngStart = Me.[A3]
    'count the used range I prefer not to use current region rows count
    Do While rngStart.Value <> vbNullString
        nInt_i = nInt_i + 1
        Set rngStart = rngStart.Offset(1, 0)
    Loop
    Set rngStart = Me.[G3]
    ' I got one column with the subject code and one with its designation, I want both in the same column
    For nInt_j = 0 To nInt_i - 1
        rngStart.Offset(nInt_j, 1).Value = rngStart.Offset(nInt_j, 0).Value & " " & rngStart.Offset(nInt_j, 1).Value
    Next nInt_j
    Set rngStart = Nothing
    'many students failed the same subject, I want the subjects only once
    RemoveDuplicates (nInt_i)
End Sub

Private Sub RemoveDuplicates(ByVal nInt_i As Integer)
Dim nInt_k As Integer
Dim nIntRow As Integer
Dim strData As String
Dim rngStart As Range
Dim dMyDic As New Dictionary
    Set rngStart = Me.[H3]
    nIntRow = 3
    For nInt_k = 0 To nInt_i - 1
        strData = rngStart.Offset(nInt_k, 0)
        If Not dMyDic.Exists(strData) Then
'the data doesn't exist in the dictionary, so I add it and keep it in my list
            dMyDic.Add strData, strData
'I erase the data
            rngStart.Offset(nInt_k, 0).Value = vbNullString
'copy the data to the next available line (could be the same line)
            Me.Range("H" & nIntRow).Value = strData
            nIntRow = nIntRow + 1
        Else
'the data already exists, so I erase it without incrementing the row number
            rngStart.Offset(nInt_k, 0).Value = vbNullString
        End If
    Next
End Sub

'Note: You will need to add a reference to "Microsoft Scripting Runtime" as the code uses its Dictionary object.
'To do this, select Tools from the toolbar, then select "References", and then select "Microsoft Scripting Runtime"

Good luck