vba 在excel中取消过滤表中的所有列并将过滤器保留在列中的正确vba是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44436897/
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
What is the correct vba to unfilter all columns in a table in excel and keep the filters in the column?
提问by Motorhead1308
I have a table full of data that I have a drop down list that when you select the drop down item it filters the column in a table.
我有一个充满数据的表格，我有一个下拉列表，当您选择下拉项目时，它会过滤表格中的列。
The problem I have is I want to have a simple reset button to unfilter the column. It's as simple as that. I don't want the filters to disappear though.
我遇到的问题是我想要一个简单的重置按钮来取消过滤列。就这么简单。我不希望过滤器消失。
I have tried a boat load of solutions
我已经尝试了很多解决方案
For reference I used another submission to this site which did not work I got a "400" error message VBA Unfilter range
作为参考，我使用了另一个提交给这个网站的文件，但它不起作用我收到了“400”错误消息 VBA Unfilter range
Worksheets("Sheet1").ShowAllData
Another solution I tried
我试过的另一个解决方案
With ActiveSheet
.Cells.EntireColumn.Hidden = True
.Cells.EntireRow.Hidden = True
.AutoFilterMode = True
End With
There has to be something super simple I'm not accouting for here.
必须有一些超级简单的东西，我没有在这里考虑。
采纳答案by Motorhead1308
I was able to figure it out. I'm not sure of the exact science behind it but the range of the table has to be selected before anything.
我能够弄清楚。我不确定它背后的确切科学，但必须先选择表格的范围。
Sub Unfilter()
'Unfilter all table columns
ActiveSheet.Range("R15:BH1072").Select
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub
回答by Diogo Cuba
Note that if you are using a TABLE instead of a RANGE you will have to corretcly reference the table because it belongs to a different object collection:
请注意，如果您使用的是 TABLE 而不是 RANGE，则必须正确引用该表，因为它属于不同的对象集合：
Using a table: ActiveSheet.ListObjects("Table288").Range.AutoFilter Field:=1
使用表格： ActiveSheet.ListObjects("Table288").Range.AutoFilter Field:=1
Using a range: just the range: ActiveSheet.Range("$P$6:$R$10").AutoFilter Field:=1
使用范围：只是范围： ActiveSheet.Range("$P$6:$R$10").AutoFilter Field:=1
Note: I used this via Macro recorder to to show the example, your code should be tweaked to fit your needs.
注意：我通过宏记录器使用它来显示示例，您的代码应该调整以满足您的需求。
