Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Filter using array and xlFilterValues

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...