Deleting rows "not like" a pattern

[andielangley]

I have an excel spreadsheet containing a list of text strings in column A. What I want to do is write a loop to delete all rows until I find one that matches a specific pattern. The pattern is only part of the text string, not the whole line. I have this so far

Range("A1&quot.Select
Do While ActiveCell.Value <> &quot;*rows)*&quot;
Range(&quot;A1&quot.EntireRow.Delete
Range(&quot;A1&quot.Select
Loop

which runs forever, presumably because the condition is never met. How do I correct this so that all rows prior to the one containing &quot;rows)&quot; are deleted? I'm sure it's obvious but I just can't see it!

 

[TonyJollans]

Hi andielangley,

To match a pattern you must use the Like operator ..

 Do While

Not

 ActiveCell.Value

Like

 "*rows)*"

That said, a loop like this will be pretty inefficient. As one alternative, have you considered using Find and then deleting everything above?

Enjoy,
Tony

 

[andielangley]

That sounds like a much better method, any tips on how to go about it? What I want to end up with is the contents of the string that is found in a string variable and all the above rows deleted.

Thanks

 

[TonyJollans]

Hi andielangley,

Try this ..

Dim R As Range, S as string
Set R = Columns("A:A").Find(What:="*rows)*", After:=[C65536], _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchDirection:=xlNext, MatchCase:=False)
S = R.Value
Range(Rows(1),Rows(R.Row-1)).Delete

Enjoy,
Tony