Excel 365中SpecialCells(xlCellTypeVisible)返回隐藏单元格问题求助

问题情况 #

使用Microsoft® Excel® for Microsoft 365 MSO（版本2208 Build 16.0.15601.20644）64位版本，给ListObject应用「2007年12月31日之后」的日期筛选（也试过手动取消早年选项），但VBA代码中用SpecialCells(xlCellTypeVisible)获取可见范围时，始终返回整个ListObject的Range，而非仅可见行。

核心代码逻辑如下：

Set oAll = Sheet2.ListObjects(1).Range
iCol = Sheet2.ListObjects(1).ListColumns(ColName).Index
On Error Resume Next
    Set oVisRng = oAll.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not oVisRng Is Nothing Then
    For Each oArea In oVisRng.Areas
        For Each oRng In oArea.Rows
        {do stuff}
        Next oRng
    Next oArea
End if

添加监视项Sheet2.ListObjects(1).Range.SpecialCells(xlCellTypeVisible).Address发现：代码执行前显示正确的可见单元格地址，但执行后变成包含隐藏行的整个范围，且代码终止后状态仍保持。尝试过使用ListColumns范围、.DataBodyRange等，均无效。

解决方法 #

方法1：遍历ListRows判断可见性 #

绕过SpecialCells的缓存问题，直接遍历ListObject的每一行，通过行的Hidden属性判断是否可见：

Dim lo As ListObject
Dim lr As ListRow
Dim targetCol As ListColumn

Set lo = Sheet2.ListObjects(1)
Set targetCol = lo.ListColumns(ColName)

For Each lr In lo.ListRows
    If Not lr.Range.Hidden Then
        ' 这里处理目标列的单元格值，示例：
        Debug.Print targetCol.DataBodyRange(lr.Index).Value
    End If
Next lr

方法2：强制刷新筛选后再获取范围 #

在调用SpecialCells前，手动刷新ListObject的筛选状态，避免Excel缓存异常：

Dim lo As ListObject
Dim oAll As Range
Dim oVisRng As Range

Set lo = Sheet2.ListObjects(1)
' 强制刷新筛选
lo.AutoFilter.ApplyFilter

Set oAll = lo.DataBodyRange
If Not oAll Is Nothing Then
    On Error Resume Next
        Set oVisRng = oAll.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not oVisRng Is Nothing Then
        ' 处理可见行，示例：
        For Each oArea In oVisRng.Areas
            For Each oRng In oArea.Rows
                Debug.Print oRng.Cells(1, lo.ListColumns(ColName).Index).Value
            Next oRng
        Next oArea
    End If
End If

方法3：排查合并单元格干扰 #

如果ListObject范围内存在合并单元格，会导致SpecialCells识别错误。先取消所有合并单元格再测试：

Sheet2.ListObjects(1).Range.UnMerge

内容的提问来源于stack exchange，提问作者pdtcaskey