Search code examples
vbaalgorithmexcelunique

vba: get unique values from array

Is there any built-in functionality in 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?

Solution

  • 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