筛选后遍历可见单元格的VBA For-Each循环异常问题

我在对工作表应用筛选条件oCel后，尝试遍历指定区域的可见单元格。当筛选后可见行数量大于1时，代码运行正常；但仅存在1个可见行时，程序会选中整个工作表作为Range并逐个Cell循环。

我试过多种方法排查，但没找到根源，希望得到更健壮、简洁的解决方案，不想用太多IF判断。

错误代码 #

With im_SH
    .Range("1:1").AutoFilter Field:=.Range("B1").Column, Criteria1:=oCel
    For Each mCel In .Range("U2:U" & .Range("U" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
            If db_SH.Cells(dr, "E") = "" Then
                db_SH.Cells(dr, "E") = mCel.Offset(0, -4)
            Else
                db_SH.Cells(dr, "E") = db_SH.Cells(dr, "E") & ", " & mCel.Offset(0, -4)
            End If
    Next mCel
End With

现有可行代码（含IF判断） #

Dim myR As Range
With im_SH
    .Range("1:1").AutoFilter Field:=.Range("B1").Column, Criteria1:=oCel
    Set myR = .Range("U2:U" & .Range("U" & .Rows.Count).End(xlUp).Row)
    
    If myR.Row = 1 Then GoTo eJump
    
    If myR.Cells.Count = 1 Then
        db_SH.Cells(dr, "E") = myR.Offset(0, -4)
    Else
        For Each mCel In myR.SpecialCells(xlCellTypeVisible)
            If db_SH.Cells(dr, "E") = "" Then
                db_SH.Cells(dr, "E") = mCel.Offset(0, -4)
            Else
                db_SH.Cells(dr, "E") = db_SH.Cells(dr, "E") & ", " & mCel.Offset(0, -4)
            End If
        Next mCel
    End If
eJump:
End With

更简洁的解决方案 #

方案1：错误处理+范围校验 #

利用错误捕获处理SpecialCells的异常场景，同时校验范围是否存在，无需额外IF判断单元格数量：

Dim visRng As Range
With im_SH
    .Range("1:1").AutoFilter Field:=.Range("B1").Column, Criteria1:=oCel
    ' 捕获SpecialCells可能的错误（如无可见单元格）
    On Error Resume Next
    Set visRng = .Range("U2:U" & .Range("U" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' 仅当存在可见单元格时才遍历
    If Not visRng Is Nothing Then
        For Each mCel In visRng
            If db_SH.Cells(dr, "E") = "" Then
                db_SH.Cells(dr, "E") = mCel.Offset(0, -4)
            Else
                db_SH.Cells(dr, "E") = db_SH.Cells(dr, "E") & ", " & mCel.Offset(0, -4)
            End If
        Next mCel
    End If
End With

方案2：用Intersect限制范围 #

通过Intersect确保最终处理的范围始终是原目标区域内的可见单元格，避免SpecialCells返回超出预期的范围：

With im_SH
    .Range("1:1").AutoFilter Field:=.Range("B1").Column, Criteria1:=oCel
    Dim targetRng As Range
    Set targetRng = .Range("U2:U" & .Range("U" & .Rows.Count).End(xlUp).Row)
    
    Dim visRng As Range
    ' 取目标区域与可见单元格的交集，确保范围正确
    Set visRng = Intersect(targetRng, targetRng.SpecialCells(xlCellTypeVisible))
    
    If Not visRng Is Nothing Then
        For Each mCel In visRng
            If db_SH.Cells(dr, "E") = "" Then
                db_SH.Cells(dr, "E") = mCel.Offset(0, -4)
            Else
                db_SH.Cells(dr, "E") = db_SH.Cells(dr, "E") & ", " & mCel.Offset(0, -4)
            End If
        Next mCel
    End If
End With

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