Loading an array with only unique values

Question

I have a range I am looping through in VBA:

For Lrow = Firstrow To Lastrow Step 1
        With .Cells(Lrow, "E")
            If Not IsError(.Value) Then

            End If
        End With
    Next Lrow

Within that if statement I need to load an array with each value only once

MB-NMB-ILA
MB-NMB-ILA
MB-NMB-STP
MB-NMB-STP
MB-NMB-WAS
MB-NMB-WAS
MB-NMB-WAS

So for the array I only want MB-NMB-ILA, MB-NMB-STP, and MB-NMB-WAS

Can anyone help me out, my brain isn't working right on a Monday! Thanks

You can use a dictionary to take unique values and then copy them back into your array (or use directly the dictionary, the way you prefer). Notice this question is already answered [HERE](http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array). Rgds — Tiago Cardoso, Mar 21 '11 at 19:28

score 1 · Answer 1 · answered Mar 28 '11 at 02:09

You could use filter to test if something exists in the array.

Dim arr As Variant: arr = Array("test1", "test2", "test3")
If UBound(Filter(arr, "blah")) > -1 Then
    Debug.Print "it is in the array"
Else
    Debug.Print "it's not in the array"
End If

You could also use a collection and write a sub to add only unique items to the collection

Dim col As New Collection
Sub addIfUnique(sAdd As String)
    Dim bAdd As Boolean: bAdd = True
    If col.Count > 0 Then
        Dim iCol As Integer
        For iCol = 1 To col.Count
            If LCase(col(iCol)) = LCase(sAdd) Then
                bAdd = False
                Exit For
            End If
        Next iCol
    End If
    If bAdd Then col.Add sAdd
End Sub
Private Sub Command1_Click()
    Dim a As Integer
    Dim b As Integer
    For a = 1 To 10
        addIfUnique "item " & a
        For b = 1 To 10
            addIfUnique "item " & b
        Next b
    Next a
    For a = 1 To col.Count
        Debug.Print col(a)
    Next a
End Sub

score 0 · Answer 2 · answered Mar 21 '11 at 21:49

Suppose I have the following in cell A1 to A5 and want an array of unique values i.e. {a,b,c,d}

        A
1      "a"
2      "b"
3      "c"
4      "c"
5      "d"

The follow two pieces of code will help achieve this:

CreateUniqueArray - get val from each cell and add to array if not already in array

IsInArray - utility function to check if value in array by performing simple loop

I have to say that this is the brute force way and would welcome any improvements...

Sub Test()
    Dim firstRow As Integer, lastRow As Integer, cnt As Integer, iCell As Integer
    Dim myArray()
    cnt = 0
    firstRow = 1
    lastRow = 10

    For iCell = firstRow To lastRow
        If Not IsInArray(myArray, Cells(iCell, 1)) Then
            ReDim Preserve myArray(cnt)
            myArray(cnt) = Cells(iCell, 1)
            cnt = cnt + 1
        End If
    Next iCell
End Sub

Function IsInArray(myArray As Variant, val As String) As Boolean
    Dim i As Integer, found As Boolean
    found = False

    If Not Len(Join(myArray)) > 0 Then
        found = False
    Else
        For i = 0 To UBound(myArray)
            If myArray(i) = val Then
               found = True
            End If
        Next i
    End If
    IsInArray = found
End Function

Loading an array with only unique values

2 Answers2

Linked