问题

I'm trying to get a unique combination of columns B and C using Excel VBA.

我正在尝试使用Excel VBA获取列B和C的唯一组合。

I'm doing this to then be able to automatically filter from columns B and C and send an email for each unique combination of the two. What is the best way to obtain the unique combination?

我这样做是为了能够自动从列B和C进行筛选，并为两者的每个唯一组合发送电子邮件。如何获取唯一组合的最佳方法？

I'm thinking of something along the lines of
put unique combination of B and C into a 2-dimensional list "L"

我正在考虑类似以下方式：
将列B和C的唯一组合放入二维列表"L"中

for i,j in L do:
filter criteria B = "i"
filter criteria C = "j"
run my email macro
end;

对于L中的每个i和j：
筛选条件B = "i"
筛选条件C = "j"
运行我的电子邮件宏
结束；

for i,j in l do:
    filter criteria B = "i"
    filter criteria C = "j"
    run my email macro 
end;

答案1

得分: 3

I always find unique values by attempting to add them to a collection while also using them as the key. Because items in a collection each must have a unique key, only unique items will be allowed into the collection. Here is the code:

Sub uniqueFruitCombo()
    Dim rng As Range, arr() As Variant, uniqueFruitNumberCollection As Collection, delimiter As String
    Set rng = Range("B2:C8")
    arr = rng
    Set uniqueFruitNumberCollection = New Collection
    
    delimiter = "!" 'specify delimiter here
    
    Dim val As String
    For i = 1 To UBound(arr)
        val = arr(i, 1) & delimiter & arr(i, 2)
        On Error Resume Next
            uniqueFruitNumberCollection.Add val, val
        On Error GoTo -1
    Next i
    
    For Each x In uniqueFruitNumberCollection
        Debug.Print "B value: " & Split(x, delimiter)(0) & ", C value: " & Split(x, delimiter)(1)
    Next x
End Sub

output:

B value: apple, C value: 1
B value: banana, C value: 1
B value: banana, C value: 2
B value: apple, C value: 3
B value: banana, C value: 4

Sub uniqueFruitCombo()
    Dim rng As Range, arr() As Variant, uniqueFruitNumberCollection As Collection, delimiter As String
    Set rng = Range("B2:C8")
    arr = rng
    Set uniqueFruitNumberCollection = New Collection
    
    delimiter = "!" 'specify delimiter here
    
    Dim val As String
    For i = 1 To UBound(arr)
        val = arr(i, 1) & delimiter & arr(i, 2)
        On Error Resume Next
            uniqueFruitNumberCollection.Add val, val
        On Error GoTo -1
    Next i
    
    For Each x In uniqueFruitNumberCollection
        Debug.Print "B value: " & Split(x, delimiter)(0) & ", C value: " & Split(x, delimiter)(1)
    Next x
End Sub

B value: apple, C value: 1
B value: banana, C value: 1
B value: banana, C value: 2
B value: apple, C value: 3
B value: banana, C value: 4