VBA动态数组含大量空值引发「未找到单元格」错误求助

错误原因 #

1. 动态范围引用不明确：Cells(Rows.Count, "B")未指定工作表，默认取当前活动工作表的B列，若活动表不是Sheet1，会错误包含大量空行到数组中。
2. 空值触发无效筛选：数组里的空值会让Criteria1:=item变成空字符串，此时筛选后可能无匹配的可见单元格，调用SpecialCells(xlCellTypeVisible)就会抛出「未找到单元格」错误。
3. 循环逻辑冗余且有隐患：外层逐行遍历完全多余，删除行后LastRowA已失效，继续循环会导致不必要计算甚至错误。

解决方案 #

步骤1：修正动态范围的工作表引用

确保取的是Sheet1的B列数据范围，避免跨表错误：

With Worksheets("Sheet1")
    ArrCategory = .Range("B8:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
End With

步骤2：过滤数组中的空值

剔除数组里的空值，只保留需要删除的有效项：

' 过滤空值，生成有效数组
Dim validItems As Collection
Set validItems = New Collection
Dim cellVal As Variant
For Each cellVal In ArrCategory
    If Not IsEmpty(cellVal) And cellVal <> "" Then
        validItems.Add cellVal
    End If
Next cellVal

步骤3：优化删除逻辑

遍历有效项，每次筛选后先判断是否有可见数据行，再执行删除，避免空筛选报错：

Dim item As Variant
For Each item In validItems
    lo1710.Range.AutoFilter Field:=5, Criteria1:=item
    ' 先检查是否有可见的数据行
    On Error Resume Next
    Dim visibleRows As Range
    Set visibleRows = lo1710.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleRows Is Nothing Then
        Application.DisplayAlerts = False
        visibleRows.Delete
        Application.DisplayAlerts = True
    End If
    lo1710.AutoFilter.ShowAllData
Next item

完整修正代码

Sub FilterAndDeleteRows()
    Dim ArrCategory As Variant
    Dim validItems As Collection
    Dim item As Variant
    Dim visibleRows As Range
    
    ' 获取Sheet1中B8开始的动态数据范围
    With Worksheets("Sheet1")
        ArrCategory = .Range("B8:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With
    
    ' 过滤空值，收集有效待删项
    Set validItems = New Collection
    For Each item In ArrCategory
        If Not IsEmpty(item) And item <> "" Then
            validItems.Add item
        End If
    Next item
    
    ' 遍历有效项执行删除
    For Each item In validItems
        lo1710.Range.AutoFilter Field:=5, Criteria1:=item
        
        ' 捕获可能的无可见单元格情况
        On Error Resume Next
        Set visibleRows = lo1710.DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not visibleRows Is Nothing Then
            Application.DisplayAlerts = False
            visibleRows.Delete
            Application.DisplayAlerts = True
        End If
        
        lo1710.AutoFilter.ShowAllData
    Next item
End Sub

额外说明 #

• 原代码外层的For i = 2 To LastRowA完全没必要，筛选已经能定位所有匹配行，逐行遍历属于重复操作，还会因删除行导致行号混乱。
• 使用On Error Resume Next捕获SpecialCells的错误是安全处理方式，避免无匹配时抛出异常。

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