我如何找到同一列中每个重复项的最后一行索引？

debugcn 发表于 Dev

约翰

我正在尝试创建一个程序，以便它可以找到位于同一列内的每个重复项的最后一行索引，并存储它们的值。例如，在图中，以John，trump，alice和sarah命名的最后一行索引应该分别给我13,17,23,26。当前，我的代码只能识别重复项，因此我不仅要为我显示的图片而且要在所有情况下如何查找每个重复项的最后一行索引？

Sub Testing()

    Dim mycell As Range, RANG As Range

    With Sheets(1)

        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))

    End With

    ' For each cell (mycell) in this range (RANG)
    For Each mycell In RANG

        ' If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then

        'how do i find the last row index of each duplicate here?

    Next mycell

End Sub

非盟

可以通过多种方式完成。下面的代码（已测试）中使用的字典对象。请添加工具->参考-> Microsoft脚本运行时。

Sub Testing()
    Dim mycell As Range, RANG As Range, Dict As Dictionary, Mname As String, Rng As Range
    Set Dict = New Dictionary
    With Sheets(1)
        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    End With


    ' For each cell (mycell) in this range (RANG)
    For Each mycell In RANG
        Mname = mycell.Value
        ' If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then


        If Dict.Count > 0 And Dict.Exists(Mname) Then
        Dict(Mname) = mycell.Row()
        Else
        Dict.Add Mname, mycell.Row()
        End If

        End If
    Next mycell


    'Display result in debug window (Modify to your requirement)
    Startrow = 2
    For Each Key In Dict.Keys
    Set Rng = Sheets(1).Range("A" & Startrow & ":A" & Dict(Key))
    Startrow = Dict(Key) + 1
    ' Now may copy etc the range Rng
    Debug.Print Key, Dict(Key), Rng.Address
    Next



End Sub