Autofilter in VBA with criteria as a range of cells

1
votes

I want to use autofilter in vba to filter using a dynamic range of cell values.

ActiveSheet.Range("$A$1:$I$954092").AutoFilter Field:=1, Criteria1:=???? _
Operator:=xlFilterValues

I want to use a dynamic range of cells like 

Range("A1",Range("A1").End(xlDown))

Can you suggest how to specify this?

I have tried passing the following to Criteria1: 

Range(###).Value
Array(Range(###))

etc.

Ex.

Col1    Col2    Col3    Col4
----------------------------
 A        1       3       Y
 B        3       3       N
 A        2       2       N
 C        6       1       Y
 B        9       3       Y

I want to filter out the rows with values A & C in Col1.

3
vbaexcelautofilter
Filtering on multiple criteria is easier with advanced filter. Take a look e.g. here – xificurC
Advanced Filter works where there is a specific filter for each column of data. What I need is a filter for a single column with selection of multiple values in that column data. – Vamsi
actually advanced filter works for that too, try it out. Create a column of data with the same header, following your example Col1 and underneath it put A and C in the next rows. Now select advanced filter on your data and in filter criteria put the whole thing, with the column header. – xificurC

3 Answers

3
votes

If we use the Recorder on a small AutoFilter we see:

Sub Macro1()
    Range("A1:C20").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:=Array( _
        "Alice", "Boris", "Mike"), Operator:=xlFilterValues
End Sub

So AutoFilter wants a 1-based array for Criteria1

Say the desired list is in sheet xx in column A. We will make an array from this list:

Sub Macro11()
    Dim N As Long, r As Range
With Sheets("xx")
        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

    Range("A1:C20").AutoFilter
    ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:=ary, Operator:=xlFilterValues
End Sub
3
votes

As @xificurC mentioned, Advanced filter does the trick! I was able to use the following code to filter multiple values at once using just a reference to a range

Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace, 
    CriteriaRange:=Range("A10:A12"), Unique:=False
0
votes

One way would be to define the last row using whatever method you prefer, and then using that value in your function. Something like:

Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.Range("$A$1:$I$" & lr).AutoFilter Field:=1, Criteria1:="Foo"

I would strongly advise you to fully qualify your range references to avoid potential headaches down the road, but this should get you started.