使用多个条件查找所有结果 [英] Find all result using multiple criteria
问题描述
我不喜欢(1 )因为表中的迭代太慢,但是我可以这样做。
Excel有一个函数可以在一个步骤中返回以特定条件过滤的范围吗?像'function multipleVlookup(...)As Range'
编辑:
答案后我的代码:(感谢Alexandre )
设置tableRange =范围(A1:M& lastRow)
'过滤器
使用tableRange
调用.AutoFilter(5,test1)
调用.AutoFilter(11,test2)
调用.AutoFilter(9,myDate)
结束
设置filteredRange = tableRange.SpecialCells(xlCellTypeVisible)
'禁用过滤器
使用tableRange
调用.AutoFilter(5)
调用.AutoFilter(11)
调用.AutoFilter(9)
结束
'用此结果执行某些操作
对于每个c在f.Cells.Rows
actualRow = c.Row
如果actualRow<> 1然后
'做某事
结束如果
下一个
如果可以过滤数据,则可以使用表的范围
,并调用 SpecialCells
这样的方法:
Dim table_range as Range
Dim filtered_range as Range
设置table_range =范围(...)
table_range.AutoFilter字段:= ... criteria1:= ...
设置filtered_range = table_range.SpecialCells(xlCellTypeVisible)
这将返回一个包含原始范围的可见单元格的 Range
/ p>
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
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.
这篇关于使用多个条件查找所有结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!