VBA Macro autofilter for a range

Question

I am trying to filter a set of data based a predetermined range of variables. These will be declared by the user of the spreadsheet in a set area of the "home" worksheet. However when running the macro it will return no results. I have attempted to resolve this in multiple ways however nothing has returned the desired results.

Up until now i have tried using operators, declaring the values as an array, filtering for the range of cells which will contain the values and also wildcards.

My current code is as below, neither of these return the output i would like

Dim fliterStr As Variant
filterStr = wsHome.Range("R6:R12").Value
wsMissing.Range("G1").AutoFilter field:=7, Criteria1:=filterStr

wsMissing.Range("G1").AutoFilter field:=7, Criteria1:="*X*", Operator:=xlFilterValues, Criteria2:="*Y*"

the expected results would be the filter searching for any values matching the variables in the range of R6:R12, however currently this is returning as blank.

the second piece of code returns some but not all of the desired results. if i exclude the operator and Criteria2 then i see a different set of results that should be included but arent.

Gary's Student Gary's Student · Accepted Answer · 2019-05-04T12:33:53

The array for AutoFIlter criteria works for a 1-D array. When you pickup a chunk of a column, you are creating a 2-D array. You need to use TRANSPOSE() to convert it.

Say we have in Sheet1:

and in Sheet2:

Running:

Sub AutoDemo()
    Dim wsHome As Worksheet, wsMissing As Worksheet
    Dim rng1 As Range, rng2 As Range, wf As WorksheetFunction
    Dim filterStr

    Set wsHome = Sheets("Sheet1")
    Set wsMissing = Sheets("Sheet2")

    Set wf = Application.WorksheetFunction

    Set rng1 = wsHome.Range("A1:G20")

    Set rng2 = wsMissing.Range("R6:R12")
    filterStr = wf.Transpose(rng2)

    rng1.AutoFilter Field:=7, Criteria1:=filterStr, Operator:=xlFilterValues

End Sub

will produce:

It is TRANSPOSE() that makes the 1-D array.

VBA Macro autofilter for a range

1 Answers