Filter In Arrays

Posted: May 28, 2013 by Theodoulus in Excel, VBA
Tags: , ,

Problem Statement:

Suppose, You have a data-set like the following:

Now you want to take this data in an Array and then want to filter it on the basis of  column “Course”.


We don’t have any predefined function that can filter an array so we have to code it.

First we need to write a function which must be GENERIC ( means which can work on any data-set and many columns). And the function is here:

Option Base 1

'This Function is created to search any value in any column of an Array and to get a final Filtered Array of values
'arrInput: is the array which needs to be passed as INPUT
'strFilter: is the Criteria on which the Input Array will be searched
'lngColumn: is the Column in which the criteria is to be searched

Function FilterArray(arrInput As Variant, strFilter As String, lngColumn As Long)

    Dim lngC As Long                    'counter variable
    Dim arrOutput As Variant            'Output Array
    Dim lngC1 As Long                   'Another Counter Variable to loop through
    Dim lngC2 As Long

    lngC1 = 0

    'Checking the number of values are matched with the searching criteria
    For lngC = 1 To UBound(arrInput, 1)
        If arrInput(lngC, lngColumn) = strFilter Then
            lngC1 = lngC1 + 1
        End If
    Next lngC

    'To exit if none is matched
    If lngC1 = 0 Then
        Exit Function
    End If
    'Setting the dimensions of the array
    ReDim arrOutput(lngC1, UBound(arrInput, 2))

    lngC1 = 1
    'getting the matched rows into the output Array
    For lngC = 1 To UBound(arrInput, 1)
        If arrInput(lngC, lngColumn) = strFilter Then
            For lngC2 = 1 To UBound(arrInput, 2)
                arrOutput(lngC1, lngC2) = arrInput(lngC, lngC2)
            Next lngC2

            lngC1 = lngC1 + 1
        End If
    Next lngC
    'Assigning the Output array to the Function so that it can be used in another program
    FilterArray = arrOutput

End Function

Now You can use this function in your program to filter the “students Who have done the Course of HighSchool”:


Sub DemoFilter()

    Dim rngData As Range                        'Data Range
    Dim arrOutput As Variant                    'Output will come in this array
    Dim arrInputData As Variant                 'you have to assign the range into an Array
    Dim strCritaria As String                   'The criteria which must be matched
    Dim lngColumn As Long                       'The column number in which you want to search

    'Take the Data in a range
    Set rngData = Intersect(ActiveSheet.Range("A1").CurrentRegion, ActiveSheet.Range("A1").CurrentRegion.Offset(1))
    'assign the range in inpu array
    arrInputData = rngData
    'Set the criteria
    strCritaria = "HighSchool"
    'Set the column to search
    lngColumn = 4
    'get the output for the function in Input Array
    arrOutput = FilterArray(arrInputData, strCritaria, lngColumn)
    'Check whether the output array is empty if none of the value is mathces
    If IsEmpty(arrOutput) Then
        MsgBox " none is matched"
        Exit Sub
    End If
    'use the array in any ways you want
    ActiveSheet.Next.Range("A1").Resize(UBound(arrOutput, 1), UBound(arrOutput, 2)) = arrOutput

End Sub

And after running this code, the filtered values will be pasted at your very next sheet of the active sheet like:

  1. snb says:

    I’d prefer:

    Sub M_snb()
        sn = array_filter_snb(Sheets(1).Cells(1).CurrentRegion, "sam", 4)
        Cells(26, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
    End Sub
    Function array_filter_snb(c00, c01, y)
        c00.Name = "snb_002"
        sn = [snb_002]
        For j = 1 To UBound(sn)
            If sn(j, y) = c01 Then c02 = c02 & "|" & j
        array_filter_snb = Application.Index(sn, Application.Transpose(Split(Mid(c02, 2), "|")), [column(snb_002)])
    End Function

    Or simpler even with a dictionary

    Sub M_snb()
        sn = Sheets(1).Cells(1).CurrentRegion
        With New Dictionary
           For j = 1 To UBound(sn)
                If sn(j, 4) = "sam" Then .Add j, Application.Index(sn, j, 0)
           Sheets(1).Cells(30, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.Items, 0, 0)
        End With
      End Sub
  2. Theodoulus says:

    Hi snb,
    Thanks for the inputs.
    however we could add some checks in it like if nothing is matched with the criteria then it should give a message not an Error.
    By the way nice code……………….

  3. Dane says:

    Hi Guys,
    I like both solutions as I’m new to VBA and different techniques are more tools for the toolbox!! Thank you.

    that being said, how could I modify this to act similarly to an advanced filter i.e. if I want to filter a column of integers say >100? I attempted in my ignorance to change to a variant rather than a string and having it be “>100” on the correct column.

    a pointer in the right direction will take me light years.


Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s