vba - 删除列中重复的单元格内容

Question

我正在尝试删除单列中重复单元格的内容。我想保留该条目的第一次出现，但删除其下面的所有重复项。

我只能找到删除整行的代码，而无法清除内容。

Sub Duplicate()

With Application
    ' Turn off screen updating to increase performance
    .ScreenUpdating = False
    Dim LastColumn As Integer
    LastColumn = Cells.Find(What:="*", After:=Range("U1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    With Range("U1:U" & Cells(Rows.Count, 1).End(xlUp).Row)
        ' Use AdvanceFilter to filter unique values
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        .SpecialCells(xlCellTypeVisible).Offset(0, LastColumn - 1).Value = 1
        On Error Resume Next
        ActiveSheet.ShowAllData
        'Delete the blank rows
        Columns(LastColumn).SpecialCells(xlCellTypeBlanks).Cells.Clear
        Err.Clear
    End With
    Columns(LastColumn).Clear
    .ScreenUpdating = True
End With

End Sub

Answer 1

这是一种方法。我们从列的底部开始向上工作:

Sub RmDups()
    Dim A As Range, N As Long, i As Long, wf As WorksheetFunction
    Dim rUP As Range

    Set A = Range("A:A")
    Set wf = Application.WorksheetFunction

    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = N To 2 Step -1
        Set rUP = Range(Cells(i - 1, 1), Cells(1, 1))
        If wf.CountIf(rUP, Cells(i, 1).Value) > 0 Then Cells(i, 1).Clear
    Next i
End Sub

我们检查上面是否有重复的内容，如果有则清除单元格。之前:

及之后:

编辑#1:

对于U列:

Sub RmDupsU()
    Dim U As Range, N As Long, i As Long, wf As WorksheetFunction
    Dim rUP As Range

    Set U = Range("U:U")
    Set wf = Application.WorksheetFunction

    N = Cells(Rows.Count, "U").End(xlUp).Row

    For i = N To 2 Step -1
        Set rUP = Range(Cells(i - 1, "U"), Cells(1, "U"))
        If wf.CountIf(rUP, Cells(i, "U").Value) > 0 Then Cells(i, "U").Clear
    Next i
End Sub