VBA - 从列中获取唯一值而不使用拆分字符串

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.

但是,当我运行此代码时,arrLen是一个小于列中唯一条目实际数量的数字(我通过对唯一条目进行手动数据检查来了解这一点。)列中的数据不包含任何“|”字符,只有数字。

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?

问题是什么?是否有另一种方法可以将唯一条目的数量输入数组并确定其长度?

1 个解决方案

#1

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.

如果是一个单元格是hello,后面的单元格是ello,它不会被包含,因为ello是hello的一部分,因此是tmp字符串的一部分。

To go with your current method, change it to

要使用当前方法,请将其更改为

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

and start with an initial tmp = "|".

并以初始tmp =“|”开头。

因此,如果当前tmp是| foo | hello | bar |,则搜索| ello |,并获得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

请参阅http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm - > KeyExistsInCollection

#1