The dataset is like this

I want to delete the same value in column C and take the average of the corresponding values in column D. But it seems the Do-Until function does not check the condition?

the first check about the line number of the first duplicated No., which works fine the second check shows the pointer mover to line 6 where the value in the C column is empty, and the Do-while function should not run, but the message box still popped up and followed an error warning

i = 2

'Do while Sheets(1).Range("C" & i).Value is not empty
Do While IsEmpty(Sheets(1).Cells(i, "C").Value) = False

    If Sheets(1).Cells(i, "C").Value = Sheets(1).Cells(i   1, "C").Value Then
        StartNo = i
        MsgBox StartNo
        Do While Sheets(1).Cells(i   1, "C").Value = Sheets(1).Cells(i, "C").Value
            i = i   1
        Loop
        EndNo = i
                          
        Sheets(1).Range("D" & StartNo) = WorksheetFunction.Average(Range("D" & StartNo & ":D" & EndNo))
        Sheets(1).Rows(StartNo   1 & ":" & EndNo).Delete
        i = StartNo
    End If
    i = i   1
    Loop

CodePudding user response：

Working directly with ranges can be a little easier to follow:

Sub Tester()
    
    Dim c As Range, n As Long, v
    
    Set c = Sheets(1).Cells(2, "C") 'start cell
    Do While Len(c.value) > 0       'loop while have data
        n = 1                       'reset replicates counter
        v = c.value                 'read once...
        Do While c.Offset(n).value = v
            n = n   1               'increment replicate count
        Loop
        If n > 1 Then               'have replicates: populate average and delete unwanted rows
            c.Offset(0, 1).value = Application.Average(c.Offset(0, 1).Resize(n))
            c.Offset(1).Resize(n - 1).EntireRow.Delete
        End If
        Set c = c.Offset(1, 0)      'next row
    Loop
    
End Sub