Find all result using multiple criteria

问题:

I have a table with multiple columns and i want to filter the table using criterias and receive a Range with the Matches. (1) I know that i can easily iterate in the table using a loop or (2) I can add filter in the columns.

I don't like (1) because iteration in table is too slow, but i can do this.

Does Excel has a function that Returns a Range filtered with a certain criteria in one step? Something like 'function multipleVlookup(...) As Range'

EDIT: My Code after the answer: (Thanks Alexandre)

Set tableRange = Range("A1:M" & lastRow)

' Filter 
With tableRange
    Call .AutoFilter(5, "test1")
    Call .AutoFilter(11, "test2")
    Call .AutoFilter(9, myDate)
End With

Set filteredRange = tableRange.SpecialCells(xlCellTypeVisible)

' Disable Filter
With tableRange
    Call .AutoFilter(5)
    Call .AutoFilter(11)
    Call .AutoFilter(9)
End With

' Do something with this result
For Each c In f.Cells.Rows
    actualRow = c.Row
    If actualRow <> 1 Then
        ' Do something
    End If
Next

回答1:

If you can filter the data, you can then use the table's Range and call the SpecialCells method like this:

Dim table_range as Range
Dim filtered_range as Range

Set table_range = Range(...)
table_range.AutoFilter field:=... criteria1:=...
Set filtered_range = table_range.SpecialCells(xlCellTypeVisible)

This returns a Range object containing just the visible cells of the original range.