0

I'm using the following code to save all unique values in a column of strings into an array, and then I get the amount of unique values by counting the length of the array.

Dim tmp As String
Dim prNumbers() As String
Dim arrLen As Integer
Dim lastRow As Integer
Dim txt As String

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Dim rngPR As Range
Set rngPR = Range("B2:B" & lastRow)

If Not rngPR Is Nothing Then
    For Each cell In rngPR
        If (cell <> "") And (InStr(tmp, cell) = 0) Then
            tmp = tmp & cell & "|"
        End If
    Next cell
End If

If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)

prNumbers = Split(tmp, "|")

'Find the array length
arrLen = UBound(prNumbers) + 1

However, when I run this code, arrLen is a number smaller than the actual amount of unique entries in the column (I know this by doing a manual data check for unique entries.) Data in the column does not contain any "|" characters, only numbers.

What could the problem be? And is there an alternative way of getting the amount of unique entries into an array and determining its length?

Improve this question
1

1 Answer 1

Reset to default
3

This check probably is the problem:

(InStr(tmp, cell) = 0)

If e.g. a cell is hello, and a later cell is ello, it won't be included because ello is part of hello and thus the tmp string.

To go with your current method, change it to 

(InStr(tmp, "|" & cell & "|") = 0)

and start with an initial tmp = "|".

So if the current tmp is |foo|hello|bar|, you search it for |ello|, and get a 0.

A more straightforward way would be to use e.g. a collection, checking each new cell if it's already contained in the collection.

See http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm --> KeyExistsInCollection

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for this. I now get more entries in my array, but still not all of the unique ones. I don't really see how that's possible! (I have 722 unique entries and get an array of only length 698). Also, should I remove the "|" at the the start again before the split?
Hmm, interesting. Can you upload a sample workbook that shows the problem to a file hoster? And yes, the starting "|" should be removed before splitting, to avoid an empty array element.
@CornelVerster Maybe you have cells with the same value but different number format? You can try cell.Text instead of cell
@Andre: gist.github.com/anonymous/2943689b68f84849df330c29610ed444
That's the range I'm looking at.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.