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

Could be done a number of way. Used dictionary object in the code (tested) below. Please add Tool -> Reference -> Microsoft Scripting Runtime.

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

修改代码以提供范围对象(从注释中可以理解)

Code modified to give a range object (as understood from comment)