Put unique values into an array and count them?

[TheWennerWoman]

Hello,

I hope someone can help. I am looking to scroll through a list of items and add them to an array - but only if they are not in the array already. I also need to ignore empty cells.

So, for example, assume this is in column E:

a
b
b
c
 
d
 
e
e

Starting at the top, I would

add a
add b
ignore the next b because it's already been captured
add c
ignore the empty cell
add d
ignore the empty cell
add e
ignore the next e because it's already been captured

I would then have a message box telling me, in this case, 5 elements are in the array.

Is this possible?

Thank you in advance.

 

[Domenic]

Try...

Option Explicit

Sub GetUniqueCount()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    
    dic.CompareMode = 1 'TextCompare (case-insensitive)
    
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
    Dim i As Long
    For i = 1 To lastRow
        If Len(Cells(i, "E")) > 0 Then
            dic(Cells(i, "E").Value) = ""
        End If
    Next i
    
    MsgBox "Unique count: " & dic.Count
    
    Set dic = Nothing
    
End Sub

Hope this helps!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Microsoft 365, you could try this:

Sub TestArray1()
  Dim a As Variant
  
  With Range("E1", Range("E" & Rows.Count).End(xlUp))
    a = Evaluate(Replace("unique(filter(#, # <> """"))", "#", .Address))
  End With
  MsgBox "Unique count = " & UBound(a) - LBound(a) + 1
End Sub

In your post, although you said you wanted the items in an array, the only output you requested was the number of items in the array, not the array itself.
If that is the actual situation (& again assuming 365) then all you should need is this:

Sub TestArray2()
  With Range("E1", Range("E" & Rows.Count).End(xlUp))
    MsgBox Evaluate(Replace("rows(unique(filter(#, # <> """")))", "#", .Address))
  End With
End Sub

 

[TheWennerWoman]

Thank you both, I have updated my account details. The reason for putting them into an array is I am hoping to use the array contents to manipulate the data a bit later.....I am sure I will need help with that too!

 

[Peter_SSs]

I have updated my account details.

Thanks for doing that.

I am hoping to use the array contents to manipulate the data a bit later..

Then my first code may be of use to you as it loads the values into an array.