VBA: Capturing unique values into an array is throwing subscript out of range

Question

I previously posted about using one dynamic list from a column on my sheet (column C in this case) as the source for data validation in column D, while requiring unique values. I was trying RemoveDuplicates before, but that method did not seem to work, so I chose to go with an array. I found Jean-Francois Corbett's post in this thread helpful, but as I am new to arrays I think I am doing something wrong.

His example was a 2-dimensional array, but my list is 1-dimensional. So I edited his methodology a bit while leaving the structure largely intact. The code below seems to work fine up until "varUnique(nUnique) = varIn(i)" near the end of the sub, at which point it throws error 9: Subscript out of range.

Sub FindUnique()
    Dim rngIn As Range
    Dim varIn As Variant
    Dim varUnique As Variant
    Dim iInCol As Long
    Dim iInRow As Long
    Dim iUnique As Long
    Dim nUnique As Long
    Dim isUnique As Boolean
    Dim i As Integer

Dim ResultingStatus As Range
Dim WhenAction As Range
Dim EvalForm As Range
Set ResultingStatus = Range("A15:Z15").Find("Resulting Status")
Set WhenAction = Range("A15:Z15").Find("When can this action")
Set EvalForm = Range("A15:Z15").Find("Evaluation Form")

    Set rngIn = Range(ResultingStatus.Offset(1, 0).Address, ResultingStatus.Offset(1000, 0).End(xlUp).Address)
    varIn = rngIn.Value

    ReDim varUnique(1 To UBound(varIn))

    nUnique = 0
    For i = LBound(varIn) To UBound(varIn)


            isUnique = True
            For iUnique = 1 To nUnique
                If varIn(i) = varUnique(iUnique) Then
                    isUnique = False
                    Exit For
                End If
            Next iUnique

            If isUnique = True Then
                nUnique = nUnique + 1
                varUnique(nUnique) = varIn(i)
            End If


    Next i

    '// varUnique now contains only the unique values.
    '// Trim off the empty elements:
    ReDim Preserve varUnique(1 To nUnique)


    Range("B28:D50").Value = varUnique
End Sub

What are the values of `nUnique` and `i` when it errors? And why not use the Dictionary method? — SJR, Sep 27 '17 at 20:34
Both are 1 when it errors. I have seen references to Dictionary elsewhere, but I am mostly unfamiliar, and from what I have read, I don't see how it gives me any advantage over Arrays. Once I've set my array of unique values, my next step will be to set a range.value = varUnique, which seems easier with arrays than dictionaries/collections. — ipenguin67, Sep 27 '17 at 20:50

score 1 · Accepted Answer · answered Sep 27 '17 at 20:56

I've only changed a couple of things, but when you write a range to an array it is automatically 2D so varIn needs a second dimension. Also tweaked your final output line to automatically adjust to the size of varUnique. The advantage of a Dictionary is that it can automatically produce unique values, i.e. it can overwrite duplicates to not repeated.

Sub FindUnique()

Dim rngIn As Range
Dim varIn As Variant
Dim varUnique As Variant
Dim iInCol As Long
Dim iInRow As Long
Dim iUnique As Long
Dim nUnique As Long
Dim isUnique As Boolean
Dim i As Integer
Dim ResultingStatus As Range
Dim WhenAction As Range
Dim EvalForm As Range

Set ResultingStatus = Range("A15:Z15").Find("Resulting Status")
Set WhenAction = Range("A15:Z15").Find("When can this action")
Set EvalForm = Range("A15:Z15").Find("Evaluation Form")
Set rngIn = Range(ResultingStatus.Offset(1, 0).Address, ResultingStatus.Offset(1000, 0).End(xlUp).Address)
varIn = rngIn.Value

ReDim varUnique(1 To UBound(varIn))

nUnique = 0
For i = LBound(varIn) To UBound(varIn)
    isUnique = True
    For iUnique = 1 To nUnique
        If varIn(i, 1) = varUnique(iUnique) Then
            isUnique = False
            Exit For
        End If
    Next iUnique
    If isUnique = True Then
        nUnique = nUnique + 1
        varUnique(nUnique) = varIn(i, 1)
    End If
Next i

'// varUnique now contains only the unique values.
'// Trim off the empty elements:
ReDim Preserve varUnique(1 To nUnique)

Range("B28").Resize(nUnique) = Application.Transpose(varUnique)

End Sub

VBA: Capturing unique values into an array is throwing subscript out of range

1 Answers1