如何清除选定区域内行内重复值？附合并单元格处理VBA代码

Got it, let's tackle your problem step by step. You already have a foundation for unmerging cells and propagating their values (critical for making your data filterable), and now we can extend that code to also clear duplicate values within each row of your selected range. Here's the complete, updated routine:

Sub MergeFiltroAndRemoveRowDuplicates()
    ' Confirm user wants to proceed with selected range
    If MsgBox("Select the desired range?", vbYesNo) = vbNo Then Exit Sub
    
    Dim mergedCell As Range, firstAddress As String
    Dim mergeValue As Variant
    Dim rowRange As Range, cell As Range
    Dim seenValues As Object
    
    Application.FindFormat.MergeCells = True
    Application.ScreenUpdating = False
    Application.EnableEvents = False ' Prevent unnecessary events during execution
    
    On Error GoTo Cleanup ' Ensure we reset settings if something goes wrong
    
    ' Step 1: Unmerge cells and fill with original merged value
    Do
        Set mergedCell = Selection.Find("", LookAt:=xlPart, SearchFormat:=True)
        If mergedCell Is Nothing Then Exit Do
        
        firstAddress = mergedCell.Address
        mergeValue = mergedCell.Value
        
        ' Unmerge and fill all cells in the merged range
        mergedCell.MergeArea.UnMerge
        mergedCell.MergeArea.Value = mergeValue
        
        ' Skip to next merged cell (avoid infinite loop)
        Set mergedCell = Selection.Find("", After:=mergedCell, LookAt:=xlPart, SearchFormat:=True)
    Loop While mergedCell.Address <> firstAddress
    
    ' Step 2: Remove duplicate values within each row of the selected range
    For Each rowRange In Selection.Rows
        Set seenValues = CreateObject("Scripting.Dictionary")
        
        For Each cell In rowRange.Cells
            If Not IsEmpty(cell.Value) Then
                If seenValues.Exists(cell.Value) Then
                    cell.ClearContents ' Clear duplicate value
                Else
                    seenValues.Add cell.Value, True ' Mark value as seen
                End If
            End If
        Next cell
    Next rowRange
    
Cleanup:
    ' Reset Excel settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.FindFormat.Clear
    Set seenValues = Nothing
    Set mergedCell = Nothing
    
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description, vbExclamation
    Else
        MsgBox "Process completed successfully!", vbInformation
    End If
End Sub

Let’s break down what this does:

• Unmerge & Fill Logic: The original core functionality is preserved—we find all merged cells in your selection, unmerge them, and fill every cell in the former merged area with the original value. This makes your data fully filterable.
• Row Duplicate Removal: For each row in your selected range, we use a dictionary to track values we’ve already seen. If a cell’s value is already in the dictionary, we clear it out; otherwise, we add it to the dictionary to mark it as "seen".
• Error Handling & Cleanup: We wrap the process in error handling to ensure Excel settings (like screen updating) get reset even if something goes wrong, and we show a success/error message at the end.

How to Use: #

1. Select the range where you want to unmerge cells and remove row duplicates.
2. Run the MergeFiltroAndRemoveRowDuplicates macro.
3. Confirm the prompt to proceed.

This should give you exactly the functionality you need for filtering-ready data with clean, duplicate-free rows.

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