VBA - Getting unique values from dynamic range

Question

I used eksortso's answer from vba: get unique values from array to get unique values from an array

Sub Trial()
    Dim myArray() As Variant
    Dim i As Long
    Dim d As Object

    myArray() = Array("Banana", "Apple", "Orange", "Tomato", "Apple", "Lemon", "Lime", "Lime", "Apple")

    Set d = CreateObject("Scripting.Dictionary")

    For i = LBound(myArray) To UBound(myArray)
        d(myArray(i)) = 1
    Next i

End Sub

This works perfectly but when I try to apply this to a range read off a worksheet, it gives me an error - Run-time error '9': Subscript out of range

Sub Clients()

    Dim Sht As Worksheet
    Dim LastRow As Long
    Dim StartCell As Range
    Dim ClientType As Variant
    Dim UniqueType As Object
    Dim i As Long

    Set Sht = Worksheets("ALL CLIENTS")
    Set StartCell = Range("F6")

    'Find Last Row
    LastRow = Sht.Cells(Sht.Rows.Count, StartCell.Column).End(xlUp).Row

    'Read Client Type Column
    ClientType = Sht.Range(StartCell, Sht.Cells(LastRow, StartCell.Column))

    Set UniqueType = CreateObject("Scripting.Dictionary")

    For i = (LBound(ClientType) - 1) To UBound(ClientType)
        UniqueType(ClientType(i)) = 1
    Next i

End Sub

Is this happening because myArray is starting at subscript 0 while ClientType is starting at 1? How do I fix this?

The problem lies in `ClientType = Sht.Range(StartCell, Sht.Cells(LastRow, StartCell.Column))`. Your `StartCell` cell comes from *active* sheet, but `Sht` - from `ALL CLIENTS` sheet. If `ALL CLIENTS` sheet is not active sheet, then you have a problem. — JohnyL, Aug 13 '18 at 20:20

Tim Williams · Accepted Answer · 2018-08-13T20:39:36.657

1

Yes ClientType will be 1-based.

Drop that -1, and also remember you're working with a 2D array:

For i = LBound(ClientType, 1) To UBound(ClientType, 1)
    UniqueType(ClientType(i, 1)) = 1
Next i

Possible failure mode when there's only a single cell in the list, since in that case you will not get a 2-D array in ClientType

edited Aug 13 '18 at 20:39

answered Aug 13 '18 at 20:10

Tim Williams

154,628
8
97
125

Can I ask a follow-up question? Some of the cells are "#N/A" which shows up as `Error 2042`. How do I get the text "#N/A". The idea is to populate it into a checkbox user-form. When I use `ClientType = Sht.Range(StartCell, Sht.Cells(LastRow, StartCell.Column))`, it says `Run-time error '13': Type mismatch` when I run `UniqueType(ClientType(i, 1)) = 1`. Is there anyway of getting the dictionary to read `.Text` rather than `.Value`? – phil_t Aug 14 '18 at 12:10

score 0 · Answer 2 · answered Jun 13 '20 at 14:46

Sub UniqueVal2Range()
    Dim Arr As New Collection, a
    Dim Item As Variant
    Dim vRng As Range

    Lr = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row    'Range Last Row
    Set vRng = Sheet1.Range("A2:A" & Lr)

    If vRng.Count > 0 Then
    '---Making Unique Values
        On Error Resume Next
            For Each a In vRng
               Arr.Add a, a
            Next
        On Error GoTo 0

    '---Printing Unique Values
        For Each Item In Arr
            Debug.Print Item
        Next Item
    End If
End Sub

VBA - Getting unique values from dynamic range

2 Answers2