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?
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