我正在尝试开发一个自定义函数来检查列表对象中的数据是否被过滤。
Public Function TestFiltered() As Boolean
Dim rngFilter As Range
Dim r As Long, f As Long
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Rows.Count
f = rngFilter.SpecialCells(xlCellTypeVisible).Count
If r > f Then TestFiltered = True
End Function
但是我收到错误“未设置对象变量”Set rngFilter = ActiveSheet.AutoFilter.Range
我的所有工作表都只有一个列表对象,但也许以某种方式更改函数以应用活动工作表中找到的第一个列表对象的范围更安全?
将列和行相乘并与它们进行比较的想法filterArea.SpecialCells(xlCellTypeVisible).Count
是比较有趣的。这就是我在此基础上成功构建的:
Public Function TestFiltered() As Boolean
Dim filterArea As Range
Dim rowsCount As Long, cellsCount As Long, columnsCount As Long
Set filterArea = ActiveSheet.ListObjects(1).Range
rowsCount = filterArea.rows.Count
columnsCount = filterArea.Columns.Count
cellsCount = filterArea.SpecialCells(xlCellTypeVisible).Count
If (rowsCount * columnsCount) > cellsCount Then
TestFiltered = True
End If
End Function
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)