如何在指定区域筛选非空单元格？VBA运行时错误9排查

原代码及错误现象 #

原函数代码 #

Function ln(sheet)
    ' this function locates the last names in a sheet
    ' and returns the range that they are contained in
    
    Dim sr As Range     ' search range
    Dim rc As Range     ' reference cell
    Dim nc As Range     ' name cell
    Dim nr As Range     ' name range
    Dim r As Range
    Dim objXl
    
    Set objXl = CreateObject("Excel.Application")
    
    If sheet = "main" Then
        Set sr = Sheet1.Range("A1:AZ80")
        Set rc = sr.Find(what:="1")
        Set nc = rc.Offset(0, 1)
        Set r = Application.Union(nc, nc.Offset(70, 0))
        Set nr = Worksheets("Sheet1").r.Cells.SpecialCells(xlCellTypeConstants).Count

触发错误 #

Run-Time Error 9: Subscript out of range

错误原因分析 #

1. 对象引用语法错误：最后一行Worksheets("Sheet1").r.Cells是完全错误的写法。VBA中不能通过工作表对象直接拼接变量名r来引用已定义的Range变量，这会被系统识别为要调用工作表中名为r的命名区域，而代码中并未创建该命名区域，因此触发下标越界。
2. 变量类型不匹配：nr被声明为Range类型，但代码试图用它接收Count返回的数值，属于类型错误，会引发后续逻辑异常。
3. 冗余资源创建：代码创建了新的Excel实例objXl但全程未使用，不仅浪费资源，还可能引发跨实例对象引用的潜在问题。
4. 未处理查找失败场景：如果sr.Find(what:="1")找不到匹配值，rc会变成Nothing，后续执行rc.Offset会直接崩溃，属于未做边界处理的隐患。

修复后的代码 #

Function ln(sheet As String) As Range
    ' 定位工作表中的姓氏并返回包含这些姓氏的非空单元格区域
    Dim sr As Range     ' 搜索范围
    Dim rc As Range     ' 参考单元格（值为"1"的单元格）
    Dim nc As Range     ' 第一个姓氏所在单元格
    Dim lastRow As Long ' 姓氏列的最后一行行号
    Dim nr As Range     ' 最终返回的姓氏区域
    
    If sheet = "main" Then
        ' 绑定当前工作簿的Sheet1，避免跨实例引用问题
        With ThisWorkbook.Worksheets("Sheet1")
            Set sr = .Range("A1:AZ80")
            ' 精确查找值为"1"的单元格，同时处理查找失败的情况
            Set rc = sr.Find(what:="1", LookIn:=xlValues, LookAt:=xlWhole)
            If Not rc Is Nothing Then
                Set nc = rc.Offset(0, 1)
                ' 获取姓氏列最后一个非空单元格的行号，替代硬编码偏移
                lastRow = .Cells(.Rows.Count, nc.Column).End(xlUp).Row
                ' 筛选姓氏列中的非空常量单元格，增加错误处理避免全空场景崩溃
                On Error Resume Next
                Set nr = .Range(nc, .Cells(lastRow, nc.Column)).SpecialCells(xlCellTypeConstants)
                On Error GoTo 0
            End If
        End With
    End If
    
    ' 返回最终定位到的姓氏区域
    Set ln = nr
End Function

修复说明 #

• 修正了错误的对象引用语法，直接通过已定义的Range变量结合工作表对象调用区域。
• 明确函数返回类型为Range，修正变量类型不匹配问题，确保逻辑一致性。
• 移除冗余的Excel实例创建，直接绑定当前工作簿的工作表，避免资源浪费和跨实例问题。
• 为Find方法增加精确匹配参数，并处理查找失败的边界场景，避免空对象调用错误。
• 改用End(xlUp)动态获取数据最后一行，替代固定偏移70行的硬编码，适配不同数据长度。
• 增加错误处理逻辑，避免当姓氏列全为空时SpecialCells触发异常。

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