Put unique values into an array and count them?

TheWennerWoman

Board Regular
Joined
Aug 1, 2019
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,825
Office Version
  1. 365
Platform
  1. Windows
Try...

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,708
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

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

TheWennerWoman

Board Regular
Joined
Aug 1, 2019
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,147,497
Messages
5,741,499
Members
423,662
Latest member
Ajmal Khursand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top