伯爵在VBA不同值的选择（大）的范围是多少？ [英] Count number of different values in chosen (large) range in VBA?

问题描述

我怎么能算不同的值（数字和字符串的混合）在选定的（大）的数量范围在VBA？

How can I count the number of different values (numbers and strings mixed) in a chosen (large) range in VBA?

我觉得这个过程是这样：
 1.读取数据为一维数组。
 2.排序阵列（快或归并排序），需要测试这
 3.简单地计算不同值的数量，如果排序的数组：若（a [1] - ;＆gt;在[我+ 1]），然后反=计数器+ 1 。

I think about this in this way:
1. Read in data into one dimensional array.
2. Sort array (quick or merge sort) need to test which
3. Simply count number of different values if sorted array : if(a[i]<>a[i+1]) then counter=counter+1.

它是解决这一问题的最有效方法是什么？

Is it the most efficient way to solve this problem?

编辑：我想这样做在Excel中

I want to do it in Excel.

推荐答案

下面是一个VBA解决方案

Here is a VBA Solution

您不需要阵列来完成这件事。你也可以使用一个集合。示例

You don't need an Array to get this done. You can also use a collection. Example

Sub Samples()
    Dim scol As New Collection

    With Sheets("Sheet1")
        For i = 1 To 100 '<~~ Assuming the range is from A1 to A100
            On Error Resume Next
            scol.Add .Range("A" & i).Value, Chr(34) & _
            .Range("A" & i).Value & Chr(34)
            On Error GoTo 0
        Next i
    End With

    Debug.Print scol.Count

    'For Each itm In scol
    '   Debug.Print itm
    'Next
End Sub

跟进

Sub Samples()
    Dim scol As New Collection
    Dim MyAr As Variant

    With Sheets("Sheet1")
        '~~> Select your range in a column here
        MyAr = .Range("A1:A10").Value

        For i = 1 To UBound(MyAr)
            On Error Resume Next
            scol.Add MyAr(i, 1), Chr(34) & _
            MyAr(i, 1) & Chr(34)
            On Error GoTo 0
        Next i
    End With

    Debug.Print scol.Count

    'For Each itm In scol
    '   Debug.Print itm
    'Next
End Sub

这篇关于伯爵在VBA不同值的选择（大）的范围是多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！