检查自动过滤范围是否完全隐藏 [英] Check if autofilter range is completely hidden

问题描述

在我的Excel工作表中，第28行中有一个过滤器，用于下面的所有其他行.
现在，我要检查是否所有行都隐藏在第28行下.因此，我从此处:

In my Excel sheet I have a filter in Row 28 for all other rows below.
Now I want to check if all rows are hidden below Row 28. Therefore, I went with the solution from this question here:

Sub Check_filter_visibility()
If Sheet1.Range("A28:A10000").SpecialCells(xlCellTypeVisible).Count > 1 Then
Sheet1.Range("A1").Value = 1
Else
Sheet1.Range("A1").Value = 2
End If
End Sub

所有这些都很好.

现在，我要包含整个 AutoFilter 范围，而不是定义的范围 Sheet1.Range("A28:A10000").
因此，我尝试使用此功能:

Now, instead of the defined range Sheet1.Range("A28:A10000") I want to include the entire AutoFilter range.
Therefore I tried to go with this function:

Sub Check_filter_visibility()
If AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Sheet1.Range("A1").Value = 1
Else
Sheet1.Range("A1").Value = 2
End If
End Sub

但是，通过这种解决方案，我会收到运行时错误424 .
有没有其他方法可以检查 AutoFilter 中的所有单元格是否都独立于定义的范围而被隐藏?

However, with this solution I get runtime error 424.
Is there any other way to make the check if all cells in the AutoFilter are hidden independent from a defined range?

推荐答案

如注释中所述，解决方案是:

As already described in the comments the solution is:

Sub Check_filter_visibility()
If Sheet1.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Sheet1.Range("A1").Value = 1
Else
Sheet1.Range("A1").Value = 2
End If
End Sub

这篇关于检查自动过滤范围是否完全隐藏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！