筛选#N/A后VBA循环可见单元格中途终止问题求助

问题根源 #

1. 循环内用Cellcount配合Offset定位行的逻辑完全错误——筛选后存在隐藏行，Offset(Cellcount)会指向隐藏行，导致后续SpecialCells无法正确获取目标行，最终循环提前终止。
2. 初始获取最后单元格的方式Range("A1").End(xlToRight).End(xlDown)在筛选状态下不准确，容易漏掉下方的可见行。
3. 重复执行Selection.AutoFilter属于冗余操作，且依赖Select会降低代码稳定性。

修复后的代码 #

' 筛选出PO查找后为#N/A的行
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

' 先关闭现有筛选（如果有），再设置新筛选
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Range("A1").CurrentRegion.AutoFilter Field:=18, Criteria1:="#N/A"

' 为WBSGL插入INDEX-MATCH公式
Dim visibleRng As Range
Dim cell As Range

' 获取R列第2行及以下的可见单元格，加错误处理避免无匹配时报错
On Error Resume Next
Set visibleRng = ws.Range("R2:R" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not visibleRng Is Nothing Then
    For Each cell In visibleRng
        ' 直接用当前单元格的行号对应Q列单元格，写入公式
        cell.Formula = "=INDEX('[" & DVOFileName & "]WBSGL accounts'!$B:$B,MATCH(" & ws.Cells(cell.Row, "Q").Address & ",'[" & DVOFileName & "]WBSGL accounts'!$A:$A,0))"
    Next cell
End If

' 可选：执行完后关闭筛选
' ws.AutoFilterMode = False

核心优化说明 #

• 直接遍历R列的可见单元格，用cell.Row直接获取当前行号，彻底避免原代码中行定位的逻辑错误。
• 用Cells(ws.Rows.Count, "A").End(xlUp).Row准确获取数据区域的最后一行，不受筛选状态影响，确保不会漏掉目标行。
• 增加错误处理，防止没有符合条件的可见单元格时代码崩溃。
• 移除所有Select操作，直接操作工作表对象，提升代码运行效率和稳定性。

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