EXCEL VBA: Is it possible to use the Filter function in VBA to filter more than 1 search string?

Lai Kan Leon 596 Reputation points


I sometimes use the Filter function to filter a 1-dimensional array.

The syntax is:
Filter(sourcearray, match, [ include, [ compare ]])

The "match" argument is used to search the string we want.
It seems that we can only search ONE string, say "cat".

Is it possible to search more than one string, say "cat" and "dog"?
How can this be done? In Excel, we can use filters to do this. I wonder if we can do this in VBA.

I tried using a variable to represent "match", like this:

ElseIf opt_1 = True Then
Dim var As String
var = "cat"
vArray2 = Filter(vArray1, var, True, vbTextCompare)

It works fine!

But how can I add "dog" to var? (so that the filtered rows contain either "cat" or "dog")


0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113K Reputation points

    Try something like this:

    Dim array1 As Variant
    array1 = Array("... cats ...", " ...Dogs ...", " ...Cats and dogs ...", "another text")
    Dim array2 As Variant
    array2 = Array()
    Dim t
    For Each t In array1
        If InStr(1, t, "cats", vbTextCompare) > 0 Or InStr(1, t, "dogs", vbTextCompare) > 0 Then
            ReDim Preserve array2(UBound(array2) + 1)
            array2(UBound(array2)) = t
        End If

    It is also possible to exclude words like "ducats" or "hotdogs".

1 additional answer

Sort by: Most helpful
  1. 60571197 1 Reputation point

    I am Sagar Singh
    I am a Student in Information & Communication Technology System Maintenance Course Study in ITI( Industrial Training Institute) Course duration 2 years and I am admission at 2020 and my course complete in 2022

    Not a Laptop & Computer

    I am study's in mobile phone

    0 comments No comments