I could use a dummy column like this
(this assumes you have a header row in row 1)

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
End Sub

or you could use find to search for all the three's, then four's, then five's

or you could use the autofilter

--
Regards,
Tom Ogilvy




"Kevin" wrote:

Thanks a bunch the AND statement did the trick. Although it doesnt run any
faster.

Is there a way to do a replace function and have it delete the rows of
unwanted text?

"John Fuller" wrote:

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.