How to delete rows that are at least 10 words long?

[sami770]

Please see the excel file attached
Each row has only a single cell, in each cell there is a short sentence.
I need a macro that check each cell and delete the entire row if the total amount of words in it is more than 9 words OR if the total amount of characters is more than 79

Any ideas?

Thx,
Sami

[nilem]

try it

Sub ert()
Dim x, i&, j&
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    x = .Value
    For i = 1 To UBound(x)
        If Len(x(i, 1)) <= 79 Then
            If UBound(Split(x(i, 1))) <= 8 Then
                j = j + 1: x(j, 1) = x(i, 1)
            End If
        End If
    Next i
    .ClearContents: .Resize(j).Value = x
End With
End Sub

[sami770]

Thanks a lot Nilem,
It works!