修改VBA代码：同时检测A列与E列重复并设置H列为0

Here's the adjusted code that will only set column H to 0 when both column A and column E values are duplicates (i.e., a row has the exact same value in A and the exact same value in E as a previous row):

Sub MarkDuplicateRows()
    Dim Cell As Range
    Dim DSO As Object
    Dim Rng As Range
    Dim compositeKey As String ' Stores combined A and E values for duplicate checks
    
    ' Define the range to process (all used cells in column A)
    Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    
    ' Initialize dictionary to track unique A+E value pairs
    Set DSO = CreateObject("Scripting.Dictionary")
    DSO.CompareMode = 1 ' Text-based comparison (case-insensitive)
    
    For Each Cell In Rng
        ' Create a unique composite key by merging A and E values
        ' Using "|" as a separator prevents accidental matches from overlapping text
        compositeKey = Cell.Text & "|" & Cell.Offset(0, 4).Text
        
        If DSO.Exists(compositeKey) Then
            ' Both A and E values match a previous row: set column H to 0
            Cell.Offset(0, 7) = 0
        Else
            ' Add the new unique pair to the dictionary
            DSO.Add compositeKey, Cell
        End If
    Next Cell
    
    ' Clean up object reference
    Set DSO = Nothing
End Sub

Key Changes Breakdown: #

• Composite Key: Instead of tracking just column A values, we combine A and E values into a single unique key. The | separator ensures we don't get false positives (e.g., A="AB" + E="C" won't be confused with A="A" + E="BC").
• Case Sensitivity: The DSO.CompareMode = 1 keeps checks case-insensitive. If you need case-sensitive matching, change this value to 0.
• Target Column: We still use Cell.Offset(0,7) to target column H (since H is 7 columns to the right of A).

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