Finding Unique values in VBA

 

VBA  - Finding Unique values

 The problem

Suppose you have a range of cells and you want to count the number of unique values in these cells.

For example, in this picture:

                                 Pic. No.1: A range of strings

 

Using the new UNIQUE function (Excel 365, available also in VBA) will not supply us with the correct answer, since the UNIQUE function isn’t case-sensitive. It won’t recognize the differences between the three cells.

The solution: Using the Dictionary 

 I’m not going to explain the Dictionary feature in VBA.

An excellent explanation (with examples) can be found here:
https://excelmacromastery.com/vba-dictionary/

              

Before running the code, we need to add the Microsoft Scripting Runtime library by using: Tools*References in VBA’s main menu

                     Pic. No. 2: Adding the Dictionary Library (stage 1)

 

  

In the Referenced list, select the Microsoft Scripting Runtime and press: OK

  

                  Pic. No. 3: Adding the Dictionary Library (stage 2)

 

Now you can run the code:

                     Pic. No. 4: Using the Dictionary in VBA

 

As can be seen, using this technique yields the correct answer.
There are 3 unique values in the referenced cells.

Liked the post?
please give it a "Like".