creating a loop

[JakeShipley2008]

I need to make this macro loop until it has went through all data in column
'A'. THe macro works as it is but I have to run it over and over until
complete.

Thanks in advance,




Sub Delete()
Dim Range1 As Range
Dim Range2 As Range


Set Range1 = Cells.Find(what:="*** NO OPE") 'you can fill in the conditions
Set Range2 = Cells.Find(what:="*** NO SAL")

Range(Range1.EntireRow, Range2.EntireRow).Delete
'Then to move to the cell above resultsSet Range2 = Range2.Offset(0, 0)
'Clears cells




End Sub

 

[Bob Phillips]

How does this differ from your post yesterday at 22:07 which I responded to
then?

 

[Dave Peterson]

This worked for me:

Option Explicit
Sub Delete()
Dim Range1 As Range
Dim Range2 As Range

With Worksheets("sheet1")
With .Range("A:A")
Do
Set Range1 = .Cells.Find(what:="~*~*~* NO OPE", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If Range1 Is Nothing Then
'done!
Exit Do
Else
Set Range2 = .Cells.Find(what:="~*~*~* NO SAL", _
after:=Range1, _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If Range2 Is Nothing Then
'done
Exit Do
Else
.Range(Range1, Range2).EntireRow.Delete
End If
End If
Loop
End With
End With

End Sub

I changed the *** to ~*~*~* to look for real asterisks--not wildcards.

And if there isn't a pair (top and bottom), then I don't delete anything.