Worksheet_Change事件中如何检测合并单元格Target的内容是否被删除

问题核心原因 #

1. 代码开头的Target.Cells.Count > 1判断会直接拦截合并单元格的操作：删除合并单元格内容时，触发Worksheet_Change事件的Target是整个合并区域，单元格数量必然大于1，代码直接终止执行
2. 合并单元格的实际值仅存储在合并区域的左上角单元格，直接读取Target.Value或使用IsEmpty(Target)判断都会返回错误结果，必须读取合并区域首单元格的值才能获取正确状态

修复后完整代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim realTarget As Range
    Dim lengthFromCell As Range
    Dim lengthToCell As Range
    
    ' 关闭事件防止触发连锁Change
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    
    ' 取合并区域的左上角单元格作为实际判断对象
    Set realTarget = Target.MergeArea.Cells(1, 1)
    ' 仅处理F列单元格
    If realTarget.Column <> 6 Then GoTo ErrHandler
    
    Set lengthFromCell = realTarget.Offset(0, 1)
    Set lengthToCell = lengthFromCell.Offset(1, 0)
    
    ' 清空长度单元格内容
    lengthFromCell.Value = ""
    lengthToCell.Value = ""
    
    ' 先清空原有验证
    lengthFromCell.Validation.Delete
    
    Select Case realTarget.Value
        Case "A"
            With lengthFromCell.Validation
                .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="1, 2, 3"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ErrorTitle = ""
                .ErrorMessage = ""
                .ShowError = True
            End With
        Case "B"
            With lengthFromCell.Validation
                .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="4, 5, 6"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ErrorTitle = ""
                .ErrorMessage = ""
                .ShowError = True
            End With
        Case Else
            ' 内容为空时直接删除验证即可，无需额外设置无效列表
            ' 如需保留下拉样式可自行恢复原有0值列表逻辑
    End Select
    
ErrHandler:
    ' 无论是否出错都恢复事件触发
    Application.EnableEvents = True
End Sub

补充说明 #

代码额外增加了事件开关和错误捕获，避免修改单元格内容时重复触发Change事件导致运行异常。如果需要在目标单元格为空时保留下拉框，可自行在Case Else分支添加对应的验证设置逻辑。

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