Excel VBA删除工作表区域时遇Run-time error '13'类型不匹配问题求助

Hey there! Let’s break down what’s causing that frustrating type mismatch error and fix your code to handle both single-cell changes and bulk clears smoothly.

Why the Error Happens #

When you clear multiple cells at once, the Target variable in your Worksheet_Change event stops being a single cell—it becomes a range of cells. Trying to compare Target.Value (which now returns an array of values, not a single string) to "A" throws the type mismatch error because you can’t directly compare an array to a single text value.

Fixed & Robust Code #

Here’s a revised version of your code that addresses the error, plus some best practices to make it more reliable:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Exit immediately if multiple cells are modified (prevents type mismatch)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    ' Only run logic if the change is in cell B2 (column 2, row 2)
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
        ' Turn off event triggers to avoid recursive loops when modifying columns
        Application.EnableEvents = False
        
        ' Add error handling to ensure events get re-enabled even if something goes wrong
        On Error GoTo Cleanup
        
        ' Show/hide columns F:G based on B2's value
        If Target.Value = "A" Then
            Me.Columns("F:G").Hidden = False ' Unhide columns F and G
        Else
            Me.Columns("F:G").Hidden = True ' Hide columns F and G
        End If
        
Cleanup:
        ' Re-enable events no matter what
        Application.EnableEvents = True
        ' Show error message if something failed
        If Err.Number <> 0 Then MsgBox "Oops! Error: " & Err.Description, vbExclamation
    End If
End Sub

Key Improvements & Fixes #

• Block multi-cell changes upfront: The check Target.Cells.CountLarge > 1 skips the code when you clear multiple cells, eliminating the type mismatch right away.
• Use Intersect for cleaner range checks: Instead of hardcoding Target.Column = 2 And Target.Row = 2, Intersect makes the code more readable and less likely to break if you adjust your worksheet layout.
• Ditch Select/Selection: Directly modifying Me.Columns("F:G").Hidden is faster and more reliable than selecting columns first (selecting is unnecessary and can cause issues with user selection).
• Prevent recursive loops: Disabling Application.EnableEvents stops the Worksheet_Change event from triggering again when you modify the columns, which avoids unexpected behavior or crashes.
• Safe error handling: The On Error GoTo Cleanup ensures that events are always re-enabled, even if an error occurs—so your worksheet won’t get stuck with events turned off.

If you need to handle bulk clears in a specific way (like resetting the form entirely), you can add an extra check to detect when cells are cleared and run your reset logic separately.

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