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
– JohnyL, Commented Aug 13, 2018 at 20:20

Tim Williams · Accepted Answer · 2018-08-13 20:39:36Z

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, 2018 at 20:39

answered Aug 13, 2018 at 20:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phil_t Over a year ago

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?

Gajendra Santosh · Accepted Answer · 2020-06-13 14:46:58Z

0

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

answered Jun 13, 2020 at 14:46

Gajendra Santosh

1591 silver badge4 bronze badges

Collectives™ on Stack Overflow

VBA - Getting unique values from dynamic range

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related