I am trying to Delete cell by comparing Names within the same column, If I find the same name, Then make sure name is containing bigger number after forward slash, If I find the bigger number then make sure my C column matching my criteria (NOT Playing). If it is matching then delete all cell which is a smaller number with column c saying Still Playing and keep only bigger number which is saying Not Playing. Any Ideas, I have posted my code but when I am finding a bigger number, it's going back and comparing with another cell so it's coming back to the same number. Here is my code..
Sub xm()
Dim lastrow As Long
Dim i As Long, firstD As Integer, secondD As Integer
Dim firstForward As Integer
Dim secondForward As Integer, score1 As String, score2 As String
Dim fName As String, sName As String
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 2 To lastrow
firstD = InStr(1, ActiveSheet.Cells(i, 1), "/")
fName = Left(ActiveSheet.Cells(i, 1), firstD - 1)
secondD = InStr(1, ActiveSheet.Cells(i + 1, 1), "/")
sName = Left(ActiveSheet.Cells(i + 1, 1), secondD - 1)
If fName = sName Then
firstForward = InStr(1, ActiveSheet.Cells(i, 1), "/")
score1 = Mid(ActiveSheet.Cells(i, 4), firstForward + 1)
secondForward = InStr(1, ActiveSheet.Cells(i + 1, 4), "/")
score2 = Mid(ActiveSheet.Cells(i + 1, 4), firstForward + 1)
If score1 > score2 Then
If ActiveSheet.Cells(i, 3) = "Not Playing" Then
'Delete entire cell
End If
End If
End If
i = i + 1
Next i
End Sub
Output:---