Finding largest number and deleting remaining smal

2019-08-18 08:04发布

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

enter image description here

Output:---

enter image description here

0条回答
登录 后发表回答