How to loop through a column and pick out only the unique integers?

Question

I'm new to VBA and I'm trying to count the number of unique numbers in this column. I wanted to use a list of some sort to store the seen numbers, then only add numbers that haven't been seen yet and then get the length of that list but VBA only seems to have arrays of definite lengths. Any insight?

column looks like this:

Let's say this is in column A. Where would you put the resulting 5 numbers? BTW you know that this can be done in Excel without VBA? What does array of definite lengths mean? You can resize arrays. — VBasic2008, Dec 18 '18 at 03:12

Display name · Accepted Answer · 2018-12-18T03:32:28.903

Non VBA easy option :

{=SUM(1/(COUNTIF(A1:A17,A1:A17)))}

Array formula to be validated with CTRL + SHIFT + ENTER

VBA easy option :

Option Explicit

Sub count_unique()

    Dim D1 As Object: Set D1 = CreateObject("scripting.dictionary")
    Dim R0 As Range
    Dim R1 As Range: Set R1 = Range("A1:A17") 'adapt to fit your range

    For Each R0 In R1: D1(R0.Value) = R0.Value: Next R0
    MsgBox D1.Count

End Sub

How to loop through a column and pick out only the unique integers?

1 Answers1