vba - 查找并删除行而不是列中的重复单元格

Question

我目前有一个 VBA 宏，它已经可以做到这一点，但不完全是我所需要的。

这是 VBA:

Sub StripRowDupes()
    Do Until ActiveCell = ""
        Range(ActiveCell, ActiveCell.End(xlToRight)).Select
        For Each Cell In Selection
            If WorksheetFunction.CountIf(Selection, Cell) > 1 Then
                Cell.ClearContents
            Else
            End If
        Next Cell
        On Error Resume Next
        Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
        ActiveCell.Range("A2").Select
    Loop
End Sub

以及一个示例工作表数据(dog 和 ship 在每一行中都是重复的):

A   | B    |   C    |   D
dog | cat  |  goat  |  dog
car | ship |  plane |  ship

运行此宏后，它会从行中删除重复项的第一个实例，结果如下所示:

A   | B     |   C
cat | goat  |  dog
car | plane |  ship

我需要删除重复项的最后一个实例，而不是第一个，以获得以下结果:

A   | B    |   C
dog | cat  |  goat
car | ship |  plane

要在当前 VBA 脚本中更改哪些内容才能获得所需的结果？

Answer 1

更新:

Sub StripRowDupes()
    Dim c As Range, rng As Range
    Dim lastcol As Long
    Dim i As Long
    Dim rngToDel As Range, temp As Range

    Application.ScreenUpdating = False

    Set c = ActiveCell

    Do Until c = ""
        lastcol = Cells(c.Row, Columns.Count).End(xlToLeft).Column
        Set rng = c.Resize(, lastcol - c.Column + 1)

        For i = lastcol To c.Column Step -1
            If WorksheetFunction.CountIf(rng, c.Offset(, i - 1)) > 1 Then c.Offset(, i - 1).ClearContents
        Next i

        On Error Resume Next
        Set temp = rng.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

        If Not temp Is Nothing Then
            If rngToDel Is Nothing Then
                Set rngToDel = temp
            Else
                Set rngToDel = Union(rngToDel, temp)
            End If
        End If

        Set c = c.Offset(1)
        Set temp = Nothing
    Loop

    If Not rngToDel Is Nothing Then rngToDel.Delete Shift:=xlToLeft

    Application.ScreenUpdating = True
End Sub