0 votes
in VBA by Expert (916 points)

Hello everyone

In the code I expect to get the unique items in range (A1:A7) to be 3

The values in the range are A, B, C, A, B.C, a

Sub Test()
    Dim v, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    
    For Each v In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Not dic.Exists(v) Then dic.Add v, v
    Next v
    
    MsgBox "Unique Count: " & dic.Count
End Sub

I got the result 7 not 3 or even 4 .. How can I control the case sensitive and also how to get the number of unqiue items?

3 Answers

+1 vote
by Expert (805 points)
selected by
 
Best answer

Try this:

Sub Test()
    Dim v As Range, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    
    For Each v In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Not dic.Exists(v.Value) Then dic.Add v.Value, v.Value
    Next v
    
    MsgBox "Unique Count: " & dic.Count
End Sub

 

by Expert (916 points)

Thank you very much JWoolley 

As I have noticed, you have declared v as range instead of Variant and this worked ..

So you inspired me to modify the original code in this line

For Each v In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

and this worked fine instead of declaring the v as Range ..

Thanks a lot for awesome help

+2 votes
by Beginner (8 points)
Sub Unique_Value_Using_Dictionary_Object()
    'prakash b bajaj
    Dim it, y
    With CreateObject("scripting.dictionary")
        .CompareMode = 1: ' with comparemode = 0 it will print 8 values
        For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
            y = .Item(it)
        Next

        Debug.Print .Count ' 2 unique keys

        Debug.Print Join(.Keys, vbLf)
    End With
End Sub
by Expert (916 points)
Thanks a lot Prakash for sharing.
+1 vote
by Beginner (16 points)

This worksheet formula will return 3 for your range

=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

 

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...