Guy,

We are confused. If Tom's method was too slow, then my macro, which was
essentially the same as Tom's in VBA form, would also be too slow, but this
method is magnitudes slower. Why do you want to pursue this method, why not
get our suggestion working?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Guy Brown via OfficeKB.com" wrote in message
...
Tom,

you are absolutely right. I have created my own script which does almost
what I want apart from the fact that the loop, as you say, does not take
into account the deleted rows?

"Private Sub CommandButton1_Click()
Const nThreshold As Long = 400
Dim cLastRow As Long

Dim myRng As Range
Dim C, s, v, vnext
Dim count As Integer, nr As Long, z As Integer, totalcount As Integer,
newcount As Integer
With Worksheets(1)
Set myRng = .Range("C1", .Cells(.Rows.count, "C").End(xlUp))
End With
nr = myRng.Rows.count
count = 0
For s = 1 To nr + 1
Cells(s, 3).Select
v = Cells(s, 3).Value
vnext = Cells(s + 1, 3).Value
If v = vnext Then
count = count + 1
ElseIf v < vnext Then
If count 20 Then
Range(Cells(s - count, 3), Cells(s, 3)).Select
Selection.EntireRow.Delete
totalcount = (count + 1) + oldcount
oldcount = totalcount
count = 0

Else
count = 0
End If
End If
Next s

MsgBox totalcount & " Records have been deleted", , "Deleted Record Count"

End Sub"

How can I do this??

otherwise I could "Clear" the rows and then delete all blank rows - any
idea how I can do this?

--
Message posted via http://www.officekb.com