在 Excel VBA 中实现动态范围内的过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14191185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
implementing filter in a dynamic range in Excel VBA
提问by xcentaur
I have searched for this and found answers related to filters, and dynamic ranges separately, but could not figure how to put them together.
我已经搜索过这个并分别找到了与过滤器和动态范围相关的答案，但无法弄清楚如何将它们放在一起。
The structure of my excel sheet is:
我的excel表的结构是：
Data:
- Date A B C
- 02-02-2012 a1 b1 c1
- 07-07-2012 a2 b2 c2
Graphs:
- graphs generated on this sheet
数据：
- 日期 ABC
- 02-02-2012 a1 b1 c1
- 07-07-2012 a2 b2 c2
图表：
- 在此工作表上生成的图表
I am using a named dynamic range myDataRangethat returns only those rows whose data is equal to or greater than a particular reference date:
我正在使用一个命名的动态范围myDataRange，它只返回那些数据等于或大于特定参考日期的行：
- =OFFSET(Data!$C$2,COUNTIF(Data!$B:$B,"<"&Graphs!$B$1&""),0,(COUNTA(Data!$C:$C)-1-COUNTIF(Data!$B:$B,"<"&Graphs!$B$1&"")),1)
- =OFFSET(Data!$C$2,COUNTIF(Data!$B:$B,"<"&Graphs!$B$1&""),0,(COUNTA(Data!$C:$C)-1-COUNTIF(数据！$B:$B,"<"&图表！$B$1&"")),1)
On the graphs sheet I have entered a date in cell B1, for example : 05-05-2012
在图表上，我在单元格 B1 中输入了一个日期，例如：05-05-2012
This would ensure myDataRange only returns the second row of data (whos date is 07-07-2012). Out of this, I plot the column C.
这将确保 myDataRange 只返回第二行数据（日期为 07-07-2012）。为此，我绘制了 C 列。
Question: But now I also need the dynamic range to be filtered as per Column A. So if reference date is 05-05-2012 in graphs cell B1 and filter is a2, then myData range returns only one row. But if filter is 05-05-2012 and a1, then it would return 0 rows.
问题：但现在我还需要按照 A 列过滤动态范围。因此，如果图表单元格 B1 中的参考日期是 05-05-2012 并且过滤器是 a2，那么 myData 范围仅返回一行。但是如果过滤器是 05-05-2012 和 a1，那么它将返回 0 行。
Could someone pls help with this - can I modify the dynamic range itself or would I have to implement this another way? I have tried to modify the dynamic range parameters, but I keep getting error messages, as it returns no values and then Excel can't graph it. Thanks in advance :)
有人可以帮忙吗 - 我可以修改动态范围本身还是我必须以另一种方式实现？我试图修改动态范围参数，但我不断收到错误消息，因为它没有返回任何值，然后 Excel 无法绘制它。提前致谢 ：）
回答by xcentaur
I am using the range mentioned above, and the code below on a combobox I inserted on the sheet.
我正在使用上面提到的范围，并在我插入到工作表上的组合框中使用下面的代码。
here is the code:
这是代码：
Sub DropDown2_Change()
子 DropDown2_Change()
Dim selectedVal As String
With ThisWorkbook.Sheets("Graphs02").Shapes("Drop Down 2").ControlFormat
selectedVal = .List(.Value)
End With
'MsgBox selectedVal
With ThisWorkbook.Sheets("Data")
.Select
.Range("A1:BY60000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=selectedVal
End With
ThisWorkbook.Sheets("Graphs02").Select
End Sub
结束子
in this, the sheet on which I have put in the combobox (Drop Down 2) is sheet "graphs02". The sheet Data has all my data, which is filtered according to the value selected in the combobox.
在此，我放在组合框（下拉列表 2）中的工作表是工作表“graphs02”。工作表数据包含我的所有数据，这些数据根据组合框中选择的值进行过滤。
Again, I got most of this working with help of stackoverflow :)
同样，我在 stackoverflow 的帮助下完成了大部分工作:)
