Excel VBA功能优化：删除A列值时清除同行E列内容

Here's the updated code that adds the logic to clear column E when the corresponding column A value is deleted, plus some efficiency improvements:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False ' Prevent endless loop
    On Error GoTo Finalize ' Ensure events are re-enabled
    
    Dim w1 As Worksheet, w2 As Worksheet
    Dim columnAcell As Range
    Dim dCell As Range
    Dim FR As Variant
    
    Set w1 = ThisWorkbook.Worksheets("AP_Input")
    Set w2 = ThisWorkbook.Worksheets("Datakom")
    
    Application.ScreenUpdating = False
    
    For Each columnAcell In Target.Cells
        Set dCell = columnAcell.Offset(0, 3) ' Column D
        
        If IsEmpty(columnAcell.Value) Or columnAcell.Value = vbNullString Then
            ' Clear D and E when A is deleted
            dCell.ClearContents
            columnAcell.Offset(0, 4).ClearContents ' Column E
        Else
            ' Update D with Mid value from A
            dCell.Value = Mid(columnAcell.Value, 2, 3)
            
            ' Look up in Datakom and update E
            FR = Application.Match(dCell.Value, w2.Columns("A"), 0)
            If IsNumeric(FR) Then
                columnAcell.Offset(0, 4).Value = w2.Range("B" & FR).Value
            Else
                ' Clear E if no matching value is found
                columnAcell.Offset(0, 4).ClearContents
            End If
        End If
    Next columnAcell
    
Finalize:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Key Changes Explained: #

• Direct Deletion Handling: We now check if the modified cell in column A is empty. If it is, we immediately clear both columns D and E for that row (since D's value was derived from A anyway).
• Row-Specific Lookup: Instead of looping through every row in column D on every change, we only process the rows that were actually modified in column A. This makes the code much faster, especially for large datasets.
• Stale Data Prevention: If the value in column D doesn't exist in the Datakom sheet's column A, we clear column E to avoid leaving old, incorrect data.
• Safer Workbook Reference: Switched from hardcoding the workbook name to ThisWorkbook, which refers to the file containing the code—safer if you ever rename the workbook.

This should maintain your original functionality while adding the requested deletion handling smoothly.

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