Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

vba: get unique values from array

Is there any built-in functionality in vba to get unique values from a one-dimensional array? What about just getting rid of duplicates?

If not, then how would I get the unique values from an array?

like image 544
Alex Gordon Avatar asked Jun 10 '10 19:06

Alex Gordon


People also ask

How do you check if a value is in an array VBA?

Use Match() function in excel VBA to check whether the value exists in an array.

How do I get unique values in Excel?

To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.


1 Answers

This post contains 2 examples. I like the 2nd one:

Sub unique()    Dim arr As New Collection, a    Dim aFirstArray() As Variant    Dim i As Long      aFirstArray() = Array("Banana", "Apple", "Orange", "Tomato", "Apple", _    "Lemon", "Lime", "Lime", "Apple")      On Error Resume Next    For Each a In aFirstArray       arr.Add a, a    Next   On Error Goto 0 ' added to original example by PEH     For i = 1 To arr.Count       Cells(i, 1) = arr(i)    Next    End Sub  
like image 53
Doc Brown Avatar answered Oct 05 '22 18:10

Doc Brown