0

I have written a code which defines an array and then uses that array as criteria to filter a range. Here's the extract of the code. Somehow it filters out everything and does not display the filtered values.

Dim N As Long

Sheets("Calculations").Select

With Sheets("Calculations")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        ReDim ary(1 To N)
        For i = 1 To N
            ary(i) = .Cells(i, 1)
        Next i
End With

    Sheets("Data").Select
    Range(Range("A1"), Range("A1").End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    With Selection
        .AutoFilter Field:=36, Criteria1:=ary, Operator:=xlFilterValues
    End With
user1778266
  • 45
  • 1
  • 6
  • 14
  • 1
    As an aside `N = .Cells(Rows.Count, "A").End(xlUp).Row` ought to be fully qualified as `N = .Cells(.Rows.Count, "A").End(xlUp).Row` – Zerk Mar 21 '17 at 16:08
  • 2
    Are the values in column A numbers? When using a variant array as the Criteria1 with xlFilterValues, numbers must be treated as text so use `ary(i) = CStr(.Cells(i, 1).Value2)` to build your array. See [Excel VBA autofilter all but three ](http://stackoverflow.com/questions/19497659/excel-vba-autofilter-all-but-three/35120033#35120033). –  Mar 21 '17 at 16:09
  • As an aside: [How to avoid using `Select` in Excel/VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – David Zemens Mar 21 '17 at 16:10
  • See also [this answer](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) which explains more reliable ways of obtaining the "last" row or column. – David Zemens Mar 21 '17 at 16:15
  • Thanks everyone for the reading material. I'll definitely look them up! – user1778266 Mar 21 '17 at 17:07

2 Answers2

2

Are the values in column A numbers? When using a variant array as the Criteria1 with xlFilterValues, numbers must be treated as text so use ary(i) = CStr(.Cells(i, 1).Value2) to build your array. 

Dim ary As Variant

With Worksheets("sheet1").Cells(1, 1).CurrentRegion
    'with true numbers in column A this DOES NOT work
    ary = Array(1, 2, 3)
    .AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues
     'with true numbers in column A this DOES work
    ary = Array("1", "2", "3")
    .AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues
End With

Yes, this seems counter-intuative but that is how to filter for numbers using an array with xlFilterValues. Dates can present a similar issue.

  • 2
    If this is the case, OP can do `Dim ary() As String` and `ReDim ary(1 to N) As String` to avoid using `CStr` on each value. – David Zemens Mar 21 '17 at 16:23
  • Yes, that is another option. Personally, I stick with variants for my arrays but that is strictly my choice. –  Mar 21 '17 at 16:25
1

When filtering by an array you must use the transpose method:

after Criteria1:= your code should read Application.Transpose(ary)

David Zemens
  • 53,033
  • 11
  • 81
  • 130
BRCoder
  • 28
  • 9
  • 2
    Are you certain? I've tried using AutoFilter with a base-0 and base-1 array, and the Transpose method is not required in either case. – David Zemens Mar 21 '17 at 16:14
  • 2
    I believe that transposing the array is only necessary when a 2-D array is used and the array has been created from rows of cell values (e.g. ary(1 to 9, 1 to 1); not required with 2-D when array has been created from columns of cell values (e.g. ary(1 to 1, 1 to 9). A 1-D array does not require it at all. –  Mar 21 '17 at 16:33