Excel VBA：如何一次仅取消过滤一个自动过滤范围？提供代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17975616/
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
Excel VBA: How to Unfilter Only One Autofilter Range at a Time? Code Provided
提问by Nicholas DiMarcello
Thanks for coming to this thread.
感谢您来到这个主题。
What I have:
我拥有的：
-A report with an autofilter on rows A:G
- 在 A:G 行上带有自动过滤器的报告
What I need:
我需要的：
-Circumstantial code that unfilters a specific column if there is a filter on it.
- 如果特定列上有过滤器，它会取消过滤特定列的环境代码。
-Running my code below unfilters the entire range of A:G.
- 在下面运行我的代码会取消过滤 A:G 的整个范围。
-In this instance, I only want "F" unfiltered, leaving any other filters alone if they are filtered.
- 在这种情况下，我只希望“F”不被过滤，如果其他过滤器被过滤，则不理会它们。
With Sheets("DATA")
If .Range("F1").AutoFilter = True Then
ActiveSheet.Range("$A:$G826").AutoFilter Field:=6
Else
End If
End With
Any and all ideas are greatly appreciated! Thank you so much!
任何和所有的想法都非常感谢！非常感谢！
采纳答案by Doug Glancy
Try this:
尝试这个：
Sub UnFilter()
Dim ws As Excel.Worksheet
Set ws = Worksheets("DATA")
With ws
If .AutoFilterMode = True Then
If Not Intersect(.AutoFilter.Range, .Range("G1")) Is Nothing Then
.Range("$A:$G826").AutoFilter Field:=.Range("G:G").Column
End If
End If
End With
End Sub
This line in your code:
您的代码中的这一行：
If .Range("F1").AutoFilter = True
... actually turns off the filtering for the whole sheet. Instead my code checks if the sheet is filtered with:
...实际上关闭了整个工作表的过滤。相反，我的代码检查工作表是否被过滤：
If .AutoFilterMode = True Then
It then checks if the filter includes column G with:
然后它检查过滤器是否包括 G 列：
If Not Intersect(.AutoFilter.Range, .Range("G1")) Is Nothing Then
I made a couple of changes to make your code a little more flexible. It also enables Intellisense for the wsobject, which is helpful. (I always find the various Filter-related properties and methods confusing, especially without auto-completion.)
我做了一些更改，使您的代码更加灵活。它还为ws对象启用 Intellisense ，这很有帮助。（我总是发现各种Filter相关的属性和方法令人困惑，尤其是没有自动完成功能。）
回答by dendarii
This worked for me
这对我有用
Sub UnfilterColumn()
With Worksheets("DATA")
If Not Worksheets("DATA").AutoFilter Is Nothing Then
ThisWorkbook.Sheets("DATA").Range("A1").AutoFilter Field:=6
End If
End With
End Sub
回答by StefD
I just entered the simple code below in the module and it worked fine for me. You will want to alter your data range and field to match your data. I'm clearing a filter with data in columns A through M. Field 8 is column H.
我刚刚在模块中输入了下面的简单代码，对我来说效果很好。您需要更改数据范围和字段以匹配您的数据。我正在清除包含 A 列到 M 列中数据的过滤器。字段 8 是 H 列。
ActiveSheet.Range("$A:$M").AutoFilter Field:=8
