(Solved) – How do i find the last row index of each duplicates within the same column?

(solved)-–-how-do-i-find-the-last-row-index-of-each-duplicates-within-the-same-column?

I am trying to create a programme such that it can find the last row index of each duplicates that lie within the same column and store their values. For example in the picture, last row index of names with John,trump,alice and sarah should give me 13,17,23,26 respectively. Currently, my code can only identify the duplicates only so what can i do to find the last row index of each duplicate not only for the picture that i showed but also for all cases?

enter image description here

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

Leave a Reply

Your email address will not be published. Required fields are marked *