寻求按单元格末尾值删除列重复项的Excel VBA宏解决方案

Hey there! I see where you're stuck—you’ve got the last 4 characters extracted, but the deduplication isn’t working because your code tries to use the original column values instead of those trailing characters. Let’s break down the issues in your current code and fix it properly:

Issues in Your Current Code #

• You used WS.Range("A4:total") which treats total as a literal string instead of the row number variable you defined. It should be WS.Range("A4:A" & total) to reference the correct range.
• The RemoveDuplicates method targets column A directly, but we need to base deduplication on the last 4 characters of those cells, not the full value.

Solution: Use an Auxiliary Column for Deduplication #

The simplest approach is to temporarily store the last 4 characters in an auxiliary column, use that column to identify duplicates, then clean up if needed. Here’s the revised code with explanations:

Sub RemoveDuplicatesByLast4Chars()
    Dim i As Long
    Dim res As String
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim total As Long
    Dim auxCol As Long ' Column to store last 4 characters
    
    ' Set your workbook and worksheet (adjust names if needed)
    Set WB = Workbooks("MQB37W - SW Architecture Matrix_Nw")
    Set WS = WB.Sheets("SW Architecture Main - In...")
    auxCol = 2 ' Using column B as auxiliary column (change if needed)
    
    With WS
        ' Get the last row with data in column A
        total = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        ' Fill the auxiliary column with last 4 characters (handle short cells)
        For i = 4 To total
            If Len(.Cells(i, 1).Value) >= 4 Then
                res = Right(.Cells(i, 1).Value, 4)
            Else
                res = .Cells(i, 1).Value ' Keep full value if shorter than 4 chars
            End If
            .Cells(i, auxCol).Value = res
        Next i
        
        ' Remove duplicates based on the auxiliary column (delete entire row)
        .Range("A4:" & .Cells(total, auxCol).Address).RemoveDuplicates _
            Columns:=auxCol, Header:=xlNo ' Use xlYes if you have a header row at row 3
        
        ' Optional: Delete the auxiliary column if you don't need it
        .Columns(auxCol).Delete
    End With
End Sub

Key Notes #

• I renamed the subroutine to something more descriptive for clarity.
• Added handling for cells shorter than 4 characters to avoid errors.
• The RemoveDuplicates method now targets the auxiliary column, so it removes rows where trailing characters (or full short values) are duplicates.
• If your data has a header row above row 4, switch Header:=xlNo to Header:=xlYes.

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