AdvancedFilter to filter one variable at the same time in three columns (vba)

Question

I would like to filter thee columns at the same time based on one variable. I use this filter inside a loop to create pdf files that contain data based on this filter.(I removed the code of creating pdf)

Let's see the following example:

Name1   Name2   Name3

Michael George  Annet
George  Michael Michael
Michael Jorge   Annet
Jorge   Annet   Michtel

There are 3 columns with names, I would like to filter these three columns based on a variable that holds a name. So for example name = "George" then I would like to see every line that contains the name "George" The output looks then:

Name1   Name2   Name3
Michael George  Annet
George  Michael Michael

I tried the following:

Set ws1 = Worksheets("Rooster")

For i = 1 To SelectionCount           
    name = NameArray(i)      

    If ws1.FilterMode Then ws1.ShowAllData        

    ws1.Range("AB8:AD157").AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=name, _
            Unique:=False
Next i

NameArray(i) is an array that contains all names that are selected by the user. (NameArray() is a function that is called)

SelectionCount counts the number of selected cells by the user.

("AB8:AD157") is the range of three columns where Excel should search for the variable.

There is no error when running this code, but nothing is filtered. What is wrong? Or is the AdvancedFilter not the right choice to use?

I studied this thread, but I couldn't implement it : http://stackoverflow.com/questions/22430551/autofilter-on-mutliple-columns-excel-vba — F1990, Mar 01 '16 at 10:22

score 2 · Accepted Answer · edited May 23 '17 at 12:13

afaik, you cannot use an array as the parameter in a Range.AdvancedFilter method¹. However, it doesn't seem too much of a stretch to mimic the .AdvancedFilter process entirely within VBA memory and range references.

Sub pseudoAdvancvedFilter()
    Dim n As Long, fnd As Range, rng As Range, addr As String, vNAMEs As Variant

    vNAMEs = Array("george", "annet")

    With Worksheets("Rooster")
        With Range("AB8:AD157")
            .EntireRow.Hidden = False
            For n = LBound(vNAMEs) To UBound(vNAMEs)
                Set fnd = .Cells.Find(What:=vNAMEs(n), LookIn:=xlValues, LookAt:=xlWhole, _
                                      MatchCase:=False, SearchFormat:=False)
                If Not fnd Is Nothing Then
                    addr = fnd.Address
                    If rng Is Nothing Then Set rng = fnd
                    Do
                        Set rng = Union(rng, fnd)
                        Set fnd = .Cells.FindNext(After:=fnd)
                    Loop Until addr = fnd.Address
                End If
                addr = vbNullString
                Set fnd = Nothing
            Next n
            If Not rng Is Nothing Then
                'Debug.Print rng.Address(0, 0)
                .EntireRow.Hidden = True
                rng.EntireRow.Hidden = False
            End If
        End With
    End With

End Sub

After collecting all of the matches to names listed in the array with a looped Range.Find method and Union method, the entire range is hidden then the union of matched cells is used to undie the matching rows.

^{Before running pseudoAdvancvedFilter} ^{After running pseudoAdvancvedFilter}

¹ _{See Can Advanced Filter criteria be in the VBA rather than a range? for more information and alternate methods.}

Thanks for the answer! Going to try that ;) – F1990 Mar 01 '16 at 12:28 — F1990, Mar 01 '16 at 12:28

user3598756 · Answer 2 · 2016-03-01T17:45:00.057

I got from OP's Q that he wanted to hide any row not containing ALL of the names in NameArray. If that is actually his need then here's my solution

Option Explicit

Sub FilterMoreColumnsByName()

Dim iName As Long
Dim dataRng As Range, cell As Range, fnd As Range
Dim nameArray As Variant, name As Variant

nameArray = Array("george", "annet") ' <== it will be hidden any row NOT containing ALL of these names

With Worksheets("Rooster")
    For iName = LBound(nameArray) To UBound(nameArray)
        name = nameArray(iName)
        Set dataRng = .Range("AB8: AB157").SpecialCells(xlCellTypeVisible) ' so as not to loop uselessly on rows that didn't match some previous name
        Set fnd = .Cells(1, 1) ' add a "dummy" cell to prevent Union method to fail the first time -> it will be hidden -> it must be unhidden before exiting sub
        For Each cell In dataRng
            If cell.Resize(, 3).Find(What:=name, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) Is Nothing Then Set fnd = Application.Union(fnd, cell)
        Next cell
        fnd.EntireRow.Hidden = True
    Next iName
    .Rows(1).Hidden = False 'show the "dummy" cell row
End With

End Sub

AdvancedFilter to filter one variable at the same time in three columns (vba)

2 Answers2