Array problem in VBA

[KNRD87]

Hello,

I have a list of values and a subset of these values. I'd like to create an array that will consist of all values but this subset, e.g.

original list: ford, bmw, mercedes, toyota
subset: bmw, toyota
new list: ford, mercedes

Is there an easy way to do this in vba?

xl2007, win xp 32 bit

 

[pgc01]

Hi

One way is to use a dictionary object.

- you first add to the dictionary all the elements in the original list
- then delete from the dictionary all the elements in the subset
- you are left in the dictionary with the objects in your new list

 

[KNRD87]

Thx, I will look into it. I have never used the dictionary object before

 

[pgc01]

I have never used the dictionary object before

This is an example of a function similar to what you need:

' Returns an array with the elements in vSetArr that do not exist in vSubSetArr
Function ArrayMinus(vSetArr As Variant, vSubSetArr As Variant) As Variant
Dim j As Long
 
With CreateObject("Scripting.Dictionary")
    On Error Resume Next
    
    For j = LBound(vSetArr) To UBound(vSetArr)
        .Add vSetArr(j), ""
    Next j
 
    For j = LBound(vSubSetArr) To UBound(vSubSetArr)
        .Remove vSubSetArr(j)
    Next j
    
    ArrayMinus = .keys
    
    On Error GoTo 0
End With
End Function

This is a test with the values you posted. Execute:

Sub test()
Dim vOriginalListArr As Variant
Dim vSubSetArr As Variant
Dim vNewListArr As Variant
 
vOriginalListArr = Array("ford", "bmw", "mercedes", "toyota")
vSubSetArr = Array("bmw", "toyota")
 
vNewListArr = ArrayMinus(vOriginalListArr, vSubSetArr)
 
MsgBox Join(vNewListArr, ", ")
End Sub

 

[KNRD87]

Thank you pgc01!

This is exactly what I meant. I need an array to filter data (using autofilter) and so far I had to manually create them in Excel, which worked fine but requiered too much work.

 

[pgc01]

I'm glad it helped. Cheers!