Filter In Arrays

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”.

Solution:

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: