Using Autofilter in VBA

Question

I am trying to create a macro to auto filter on a named range. I have the following code:

Sub Filter()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Historical Holdings")
Set wsL = Worksheets("Control")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("Filter_Range")

vCrit = rngCrit.Value

rngOrders.AutoFilter _
    Field:=1, _
 Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues

End Sub

The macro works, but it filters everything out instead on selecting the values in the named range. Does anyone know what I am doing wrong?

You want to make an array from a range. You cannot do it like `vCrit = rngCrit.Value` You need a loop. — M--, Jun 13 '17 at 15:49
Possible duplicate of [Filtering Column Based on Range VBA](https://stackoverflow.com/questions/11872651/filtering-column-based-on-range-vba) — M--, Jun 13 '17 at 16:13
@Masoud `vCrit = rngCrit.Value` is perfectly valid - assuming `"Filter_Range"` was a named range referring to cells A5:A12 (as an example) then it would create an array dimensioned `1 To 8, 1 To 1`. The later `Application.Transpose(vCrit)` would convert that to an array dimensioned `1 To 8`. — YowE3K, Jun 13 '17 at 19:45
The macro works for me - I created a column of "filter values" on a sheet called "Control", gave it a range name of "Filter_Range", then the data on the sheet called "Historical Holdings" was correctly filtered using the filter values against column A. You may need to include a screenshot of your data - maybe there is something weird that is causing the problem. — YowE3K, Jun 13 '17 at 19:47

Using Autofilter in VBA

0 Answers0