当工作表具有过滤器时，为什么ActiveSheet.FilterMode返回False? [英] Why does ActiveSheet.FilterMode returns False when sheet has filter?

问题描述

我正在使用以下代码来尝试检测应用于表中列的过滤器，然后清除该过滤器:

I'm using the following code in an attempt to detect a filter applied to a column in a table and then clear the filter:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

根据Microsoft文档:

According to Microsoft documentation:

如果工作表包含包含隐藏行的过滤列表，则此属性为true.

This property is true if the worksheet contains a filtered list in which there are hidden rows.

情况似乎并非如此，因为ActiveSheet.Filtermode仅在选择了要应用过滤器的表格内的单元格时才返回True.

This doesn't seem to be the case since ActiveSheet.Filtermode only returns True if a cell inside the table where the filter is applied is selected.

• 第一个问题:文档是否错误? 文档

第二个问题:我唯一的选择是在表中选择一个单元格以使表达式返回True吗?

Second question: Is my only option to select a cell inside the table to get the expression to return True?

我正在使用Excel 2010的PS

PS I am using Excel 2010

对问题2的回答，基于非选择的方法来清除过滤器...

Answer to Question 2, Non-select based methods to clear filters...

If ActiveSheet.ListObjects(1).Autofilter.FilterMode Then ActiveSheet.ListObjects(1).Autofilter.Showalldata

推荐答案

我可以在Excel 2013上复制您的两个问题:FilterMode上的错误False和ShowAllData上的错误.

I can replicate both your issues on Excel 2013: both the buggy False on FilterMode and the error on ShowAllData.

针对文档是否有误，我要说缺少是说ActiveCell应该在ListObject s DataBodyRange中的一种限定.也许文档是正确的，但是这是一个尚未解决的错误.也许您可以通过文档链接更新问题?

In response to whether the documentation is wrong, I would say that it is missing a qualification to say that the ActiveCell should be in the ListObjects DataBodyRange. Perhaps the documentation is correct but that this is a bug that has not been addressed. Maybe you can update your question with a link to the documentation?

是第二个问题-我同意使用此替代方法是最明显的解决方案.使用Select似乎有点不愉快，但有时我想这是无法避免的.

Re your second question - I agree that using this workaround is the most obvious solution. It seems a bit unpleasant to use Select but sometimes I guess this cannot be avoided.

这是我使用Intersect函数检查它的方法，ActiveCell当前是否在ListObject的DataBodyRange区域:

This is how I did it using the Intersect function to check it the ActiveCell is currently in the area of the DataBodyRange of the ListObject:

Option Explicit

Sub Test()

    Dim rng As Range
    Dim ws As Worksheet
    Dim lst As ListObject

    'get ActiveCell reference
    Set rng = ActiveCell

    'get reference to Worksheet based on ActiveCell
    Set ws = rng.Parent

    'is there a Listobject on the ActiveCells sheet?
    If ws.ListObjects.Count > 0 Then
        Set lst = ws.ListObjects(1)
    Else
        Debug.Print "No table found"
        Exit Sub
    End If

    'is cell is in the DataBodyRange of ListObject?
    If Intersect(rng, lst.DataBodyRange) Is Nothing Then
        'set the ActiveCell to be in the DataBodyRange
        lst.DataBodyRange.Cells(1, 1).Select
    End If

    'now you can safely call ShowAllData
    If ws.FilterMode = True Then
        ws.ShowAllData
    End If

End Sub

编辑

进一步@orson的评论:

Edit

Further to @orson's comment:

如果跳过If Intersect(rng，lst.DataBodyRange)Nothing Then，然后使用If lst.AutoFilter.FilterMode Then lst.AutoFilter.ShowAllData End If，会发生什么情况?

What happens if you skip the If Intersect(rng, lst.DataBodyRange) Is Nothing Then and use If lst.AutoFilter.FilterMode Then lst.AutoFilter.ShowAllData End If ?

因此，您可以检查ListObject本身的FilterMode，然后，只要您引用了ListObject，就可以使用他的代码:

So, you can check the FilterMode of the ListObject itself and then as long as you have a reference to the ListObject you can use his code:

If lst.AutoFilter.FilterMode Then 
    lst.AutoFilter.ShowAllData 
End If

这篇关于当工作表具有过滤器时，为什么ActiveSheet.FilterMode返回False?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！