We help IT Professionals succeed at work.

Filter a VBA Array

1,134 Views
Last Modified: 2012-03-26
Hello all

I have a Array with two fields of values.
I need to be able to filter the array so it only contains rows/records where the second column/Field equals a certain value (ie 5).

Can this be done with out copying to a worksheet and filtering the data?

(This filtering will need to be done fairly constantly while the project is being used.
Also it's only about 300 records so pasting to a worksheet, filtering and copying back seems an overkill)

Thanks
Comment
Watch Question

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Use a collection instead of two arrays.

First define a class that looks like this
Option Explicit

Public Column1 As Integer
Public Column2 As Integer

Open in new window


Then

    Dim C As Class1
    Dim MyCollection As New Collection
    Dim lngIndex As Long
    
    'add some records to the collection for testing
    For lngIndex = 0 To 99
        Set C = New Class1
        C.Column1 = lngIndex
        ' add random numbers between 2 and 7 so that a bunch will be = 5
        C.Column2 = Int((6) * Rnd + 2)
        MyCollection.Add C
        Set C = Nothing
    Next
    
    Debug.Print MyCollection.Count & " records in the collection"
    
    'Print a few of the column 2 values to show how they're accessed
    For lngIndex = 1 To 20 ' collections start at index = 1
        Debug.Print MyCollection(lngIndex).Column2
    Next
    
    'Get rid of the 5's
    For lngIndex = 100 To 1 Step -1
        If MyCollection(lngIndex).Column2 = 5 Then
            MyCollection.Remove lngIndex
        End If
    Next
    Debug.Print MyCollection.Count & " records in the filtered collection"

Open in new window

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Why have the overhead of a Collection?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes, collections have overhead, that being that they are inherently slower than arrays, but given the manipulation that you would need to do to the two arrays that you would need, I think that the collection would compare favorably. In any case were only talking about 300 entries so it won't make any perceptible difference in either case.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
p-plater: Is your array fixed at 300 entries or is it dynamic?
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Mostly I'm curious as to why you suggested them over arrays - I don't see any benefit, especially as you'll have to repopulate the collection from the original array each time you want to filter on a different value.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
...you'll have to repopulate the collection from the original array each time you want to filter on a different value.
Not at all. Just use the collection from the beginning. In other words substitute it for the array.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
But you remove items from it to filter it - how would you then filter it differently?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I assume that p-plater is currently adding new records to his array and he would continue that practice except with the collection.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Another problem with two-dimensioal arrays is that Redim Preserve is problematic in that you can only redim the second dimension and you can't reduce the size of an array at all with Redim Preserve. But before we discuss this further we should probably wait to hear from p-plater.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Agreed. :)
I think we have different interpretations of the situation, hence differing suggestions.

Author

Commented:
Hello

Yes the Array is Dynamic - It is currently only 100 Records and when it is Filtered it can be as low as 5 records.

300 Is just a guess at a future maximum

I have said Two Fields but I will also be needing to filter arrays with more fields.

What I am doing is filtering the records that are saved in a external workbook and putting the result into a Combobox.

I then need to filter the same data  on the result of the first Combobox value to fill a second Combo Box
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
If you want to go the classes and collections route, read this article on easy filtering.
http:/A_8450.html

Alternatively, you could keep track of the high used item (index) value and use the single array.  You would iterate through the array, and move the 'filtered' items to the beginning of the array, incrementing the high used variable (starting at the LBound() value).  Of course, you would need to rebuild the array if filtering on a different value or on the other column.

If this array is populated from a data source, you should use the WHERE clause to do your filtering.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you are getting the data from another workbook, why not use a recordset in the first place? You can then just filter that as needed.
Most Valuable Expert 2012
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2012
Top Expert 2012

Commented:
Martin - Having just rewrote using the class approach ( I was only using/creating those when it took me more than 5 minutes to think about the data structure, lol) I don't think I'll ever go back to using Type, again.  Especially, if I want to remove records from the "array".

Its just too easy from here to add a sort method, a uniqueness test, a data checking method, etc... :)

Thanks for your sharing.

Dave

Author

Commented:
Hello
I am Normaly using SQL with Record sets - How do I use a record set from a worksheet and filter it?
Thanks
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.