Excel VBA问题：姓名格式转换与RemoveDuplicates重复行删除异常

Hey there! Let's tackle your two VBA issues one by one, and fix up your code to work exactly as you need it to.

Your Sheet1 stores names in the Last, First format (like Curry,Steph) tied to unique IDs. We'll use VBA's Split() function to break apart the name string, rearrange the parts, and keep the original ID fully intact.

How this fix works: #

• Split the name at the comma to separate last and first name components
• Trim any extra whitespace (in case there's a space after the comma)
• Combine the first name and last name with a space in between
• Write both the original ID and reformatted name to your ChangedNames sheet

Your current nested loop + Cells.RemoveDuplicates approach is flawed—this method isn't meant to be used inside a loop like that, which is why you're seeing partial deletions instead of full duplicate rows being removed.

The RemoveDuplicates method works directly on a specified range; you just need to tell it which column(s) to check for duplicates (e.g., your ID column) to trigger full row deletion.

Full Corrected Code #

Sub GenerateNames()
    Dim ssheet1 As Worksheet
    Dim rngen As Worksheet
    Dim rnsheet As Worksheet
    Dim changedNames As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim nameParts As Variant
    
    ' Set worksheet references
    Set ssheet1 = ThisWorkbook.Sheets("Sheet1")
    Set rngen = ThisWorkbook.Sheets("RnGen")
    Set rnsheet = ThisWorkbook.Sheets("RandomNames")
    Set changedNames = ThisWorkbook.Sheets("ChangedNames")
    
    ' Copy data to RandomNames sheet as originally intended
    rngen.Range("A3:A70").Copy rnsheet.Range("A3:A70")
    ssheet1.Range("B3:B70").Copy rnsheet.Range("B3:B70")
    
    ' --- Fix 1: Convert name format and populate ChangedNames ---
    ' Clear existing data in ChangedNames to avoid mixing old entries
    changedNames.Range("A3:B70").ClearContents
    
    ' Loop through Sheet1's data (uses dynamic last row instead of hardcoded 70)
    lastRow = ssheet1.Cells(ssheet1.Rows.Count, "B").End(xlUp).Row
    For i = 3 To lastRow
        ' Split the name at the comma character
        nameParts = Split(ssheet1.Cells(i, "B").Value, ",")
        
        ' Ensure we have both name parts before reformatting
        If UBound(nameParts) = 1 Then
            ' Write original ID to ChangedNames column A
            changedNames.Cells(i, "A").Value = ssheet1.Cells(i, "A").Value
            ' Write reformatted name (First Last) with trimmed whitespace
            changedNames.Cells(i, "B").Value = Trim(nameParts(1)) & " " & Trim(nameParts(0))
        Else
            ' Fallback: Keep original name if format is invalid (no comma)
            changedNames.Cells(i, "A").Value = ssheet1.Cells(i, "A").Value
            changedNames.Cells(i, "B").Value = ssheet1.Cells(i, "B").Value
        End If
    Next i
    
    ' --- Fix 2: Remove duplicate rows in RandomNames ---
    ' Assuming ID is in column B (adjust Columns(2) to your actual ID column)
    ' Use Header:=xlYes if your range has a header row at row 2
    rnsheet.Range("A3:B70").RemoveDuplicates Columns:=Array(2), Header:=xlNo
End Sub

Key Improvements: #

• Name conversion: Added error handling for cases where a name might not follow the Last, First format (to avoid runtime errors)
• Duplicate removal: Targets the exact range in RandomNames and uses the ID column to identify duplicates, ensuring full rows are deleted instead of partial data
• Flexibility: Replaced hardcoded row numbers with a dynamic lastRow check, so the code works even if your data length changes

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