Array problem in VBA

KNRD87

Board Regular
Joined
Jan 21, 2011
Messages
67
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.

Code:
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
I have never used the dictionary object before

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

Code:
' 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:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,288
Messages
6,171,213
Members
452,391
Latest member
BHG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top