Loading an array with only unique values

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 wi开发者_运维问答th 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 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

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