Word VBA处理医嘱表格无对应分类时Error 5941的解决请求

问题核心 #

当某类药物无数据时，直接对筛选后的空表格执行删除列等操作，会因目标对象不存在触发Error 5941，常规的Application.DisplayAlerts = False或On Error Resume Next无法覆盖对象引用阶段的错误。

修正方案 #

通过提前判断数据存在性+筛选后校验有效行的方式，彻底避免空数据场景下的对象操作错误：

步骤1：提前判断分类是否有数据 #

在复制生成分类表格前，先检查源表格中对应标记（s/c/o）是否存在，仅当有数据时才生成对应工作表。

步骤2：筛选后校验有效数据行 #

执行筛选后，确认可见行数量（排除表头），仅当有有效数据时才执行删除列操作；无数据则直接清理空表格和工作表。

完整修正代码 #

Sub ProcessDrugTables()
    Dim wsSource As Worksheet
    Dim wsStop As Worksheet, wsContinue As Worksheet, wsConsult As Worksheet
    Dim tblSource As ListObject, tblStop As ListObject, tblContinue As ListObject, tblConsult As ListObject
    Dim hasStop As Boolean, hasContinue As Boolean, hasConsult As Boolean
    
    '替换为你的源工作表和表格名称
    Set wsSource = ThisWorkbook.Worksheets("药物列表")
    Set tblSource = wsSource.ListObjects("药物表格")
    
    '判断各分类是否存在对应数据
    hasStop = Not tblSource.ListColumns(3).DataBodyRange.Find("s", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
    hasContinue = Not tblSource.ListColumns(3).DataBodyRange.Find("c", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
    hasConsult = Not tblSource.ListColumns(3).DataBodyRange.Find("o", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
    
    '处理「停用」分类
    If hasStop Then
        wsSource.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set wsStop = ActiveSheet
        wsStop.Name = "停用"
        Set tblStop = wsStop.ListObjects(1)
        
        '筛选标记为s的行
        tblStop.Range.AutoFilter Field:=3, Criteria1:="s"
        
        '校验是否有有效数据行（表头+至少1行数据）
        If tblStop.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            tblStop.ListColumns(3).Delete '删除标记列
        Else
            '无数据则清理空表格和工作表
            tblStop.Delete
            Application.DisplayAlerts = False
            wsStop.Delete
            Application.DisplayAlerts = True
        End If
    End If
    
    '处理「按医嘱服用」分类
    If hasContinue Then
        wsSource.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set wsContinue = ActiveSheet
        wsContinue.Name = "按医嘱服用"
        Set tblContinue = wsContinue.ListObjects(1)
        
        tblContinue.Range.AutoFilter Field:=3, Criteria1:="c"
        If tblContinue.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            tblContinue.ListColumns(3).Delete
        Else
            tblContinue.Delete
            Application.DisplayAlerts = False
            wsContinue.Delete
            Application.DisplayAlerts = True
        End If
    End If
    
    '处理「遵其他医生医嘱」分类
    If hasConsult Then
        wsSource.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set wsConsult = ActiveSheet
        wsConsult.Name = "遵其他医生医嘱"
        Set tblConsult = wsConsult.ListObjects(1)
        
        tblConsult.Range.AutoFilter Field:=3, Criteria1:="o"
        If tblConsult.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            tblConsult.ListColumns(3).Delete
        Else
            tblConsult.Delete
            Application.DisplayAlerts = False
            wsConsult.Delete
            Application.DisplayAlerts = True
        End If
    End If
    
    '清除源表格的筛选状态
    tblSource.AutoFilter.ShowAllData
End Sub

关键说明 #

• 用Find方法提前判断数据存在性，避免生成空工作表。
• 通过SpecialCells(xlCellTypeVisible).Rows.Count校验有效数据，确保操作对象存在。
• 无数据时直接清理资源，避免后续操作触发对象不存在错误。

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