基于多个单元格值的Excel宏过滤器 [英] Excel macro filter based on multiple cell values
本文介绍了基于多个单元格值的Excel宏过滤器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个简单的宏,可以根据A13单元格中的值过滤行.效果很好.
I have this simple macro that filters rows based on value in A13 cell. It works fine.
With ActiveSheet
.Range("B2:F5000").AutoFilter Field:=2, Criteria1:=.Range("A13")
End With
但是我需要为该过滤器应用更多的值,特别是基于两个或更多单元格的值.所以我运行这个宏:
But I need more values to be applied for this filter, specifically based on two or more cells. So I run this macro:
With ActiveSheet
.Range("B2:F5000").AutoFilter Field:=2, Criteria1:=.Range("A13:A14:A15")
End With
但是它仅过滤基于A15单元格的值.这是为什么?我已经在这里阅读了所有主题,但是没有解决此特定问题的方法.谢谢大家的帮助.Libor.
But it filters only values based on A15 cell. Why is that? I have read all the topics here, but no solution to this specific problem. Thank you all for your help. Libor.
推荐答案
- 先删除旧的自动筛选器
- 在
Field:= 1
上进行过滤.如果您的范围从B列开始,并且要在B列中进行过滤,那么这是第一个字段,而不是第二个字段. - 如果要过滤值/数字而不是文本,请使用公式而不是值进行过滤.例如
= 200
过滤数字200
.
- Remove the old AutoFilter first
- Filter on
Field:=1
. If your range starts in column B and you want to filter in column B then this is the first field not the second. - If you want to filter on values/numbers not text, filter by using a formula instead of the value. Eg
=200
to filter for number200
.
这是一个应该起作用的示例.
Here is an Example that should work.
With ActiveSheet 'better reference a sheet by its name like: Worksheets("Sheet1")
If .AutoFilterMode = True Then .AutoFilterMode = False 'remove old autofilter
.Range("B:F").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=Array("=" & .Range("A13").Value, "=" & .Range("A14").Value, "=" & .Range("A15").Value)
End With
这篇关于基于多个单元格值的Excel宏过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文