如何解决删除表格行时VBA代码的Type 13类型不匹配错误？

原代码的核心问题：

• 删除整行时，Target是多行/整行范围，intersectRange1包含R列整行单元格，直接调用Validation.Type会因部分单元格无验证规则报错
• 直接判断Target.Value会因Target是多单元格范围返回数组，触发类型不匹配

修正后的代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrorHandler ' 捕获错误，确保事件能恢复
    
    Dim rng1 As Range
    Dim intersectRange1 As Range
    Dim cell As Range
    
    Set rng1 = Me.Range("R:R")
    Set intersectRange1 = Intersect(Target, rng1)
    
    If Not intersectRange1 Is Nothing Then
        ' 遍历交集里的每个单元格，避免多单元格范围导致的问题
        For Each cell In intersectRange1
            ' 临时忽略无验证单元格的错误，避免整行删除时触发异常
            On Error Resume Next
            Dim validationType As Integer
            validationType = cell.Validation.Type
            On Error GoTo ErrorHandler
            
            If validationType = 3 Then
                ' 确保单元格值为字符串类型，过滤删除操作导致的空值/错误值
                If TypeName(cell.Value) = "String" Then
                    Select Case cell.Value
                        Case "Pending Reqs"
                            UpdateTimestamp cell.Row, "AO"
                        Case "Reqs Received"
                            UpdateTimestamp cell.Row, "AP"
                        Case "Approved"
                            UpdateTimestamp cell.Row, "AQ"
                    End Select
                End If
            End If
        Next cell
    End If

ExitSub:
    Application.EnableEvents = True
    Exit Sub
    
ErrorHandler:
    ' 错误时直接恢复事件状态并退出
    Resume ExitSub
End Sub

' 封装时间戳更新逻辑，简化主代码
Private Sub UpdateTimestamp(rowNum As Long, colLetter As String)
    With Me.Cells(rowNum, colLetter)
        If .Value = "" Then
            .Value = Now
        Else
            .Value = .Value & ", " & Now
        End If
    End With
End Sub

关键改动说明：

• 新增全局错误捕获，确保无论是否出错，Application.EnableEvents都能恢复为True，避免后续事件失效
• 遍历intersectRange1内的单个单元格，规避多单元格范围操作的异常
• 临时忽略无数据验证单元格的错误，解决整行删除时的规则不匹配问题
• 判断单元格值类型后再处理，过滤删除操作产生的空值/错误值
• 封装时间戳更新逻辑，让主代码结构更清晰

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