Do...Loop and how to end it

[lolitafontaine]

I regularly import documents from a database I use into Excel as Tex
Files, which then need to be "cleaned" up by deleteing entire rows.
have accomplished this so far via point/click/record (I'm a tota
amateur):

Do
Cells.Find(What:="-----", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False).Activate
Selection.EntireRow.Delete

Loop


What happens now, of course, is that the macro runs thorugh the entir
document but displays an error message when it can no longer find th
serach criteria. I then exit the loop, retype in the code the nex
search term, and re-launch the macro.

I have 8 search terms to define:
1) "-----"
2) "====="
3) "Page"
4) "L I S T"
5) "Location"
6) "Cycle"
7) "Portion"
8) "Price"


I want the statements (procedures? see - I don't know nuthin') to run
each in turn, advancing within the macro after each row containing th
search term has been found and deleted.

These documents all have the same rows to be deleted, but they all var
in size and length depending upon which location I run the report for.


Any help would be greatly appreciated! Thanks

 

[Jon Peltier]

There are a few variations on this. You can try any of the following,
check on line help or run a few experiments to figure out how each one
works:

Do
' code
Loop Until <something>

Do Until <something>
' code
Loop

Do
' code
Loop While <something>

Do While <something>
' code
Loop

Do
' code
If <something> then Exit Do
Loop

- Jon

 

[Mark Rosenkrantz]

There you go.

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

 

[lolitafontaine]

Thanks for your replies. I have seen both these examples in the HEL
file, but I don't know the syntax to use to define the condition.

So, if I use this format:

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop


...how do I define the condition? I have the statements, I can figur
out where to put the "Exit Do", but I don't know what verbage to use t
tell the macro "OK - you've searched, you've found, you've deleted the
all, now move on to the next statement."

That's where I'm stuck. Thanks again for the help - I've been messin
with this on and off for a coupla weeks

 

[LB]

If you want to loop through rows to the last row of the spreadsheet, try:

Do Until i=ActiveSheet.Cells.SpecialCells(xlLastCell).Row
[statements]
[Exit Do]
[statements]
i=i+1
Loop