Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I stop the find at the end of the sheet
I need to handle the first find and delete a bit
differently than the rest. After the first one, I want to repeatedly search from a few rows down from the first one until the end of the sheet, doing deletes. How do I stop the loop from continuing back to the start of the worksheet, finding text that I intentionally left behind. -------- Range("A1").Activate Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(-3, 0)).EntireRow.Delete Range("A1").Offset(4, 0).Activate Do Until Cells.Find("PAUL INC") Is Nothing Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(1, 0)).EntireRow.Delete Loop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I stop the find at the end of the sheet
"djh" wrote in message
... I need to handle the first find and delete a bit differently than the rest. After the first one, I want to repeatedly search from a few rows down from the first one until the end of the sheet, doing deletes. How do I stop the loop from continuing back to the start of the worksheet, finding text that I intentionally left behind. -------- Range("A1").Activate Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(-3, 0)).EntireRow.Delete Range("A1").Offset(4, 0).Activate Do Until Cells.Find("PAUL INC") Is Nothing Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(1, 0)).EntireRow.Delete Loop What is often done is to save the address of the starting cell and check each time to see if Find has returned to it. -- Bob Kilmer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I stop the find at the end of the sheet
-----Original Message----- "djh" wrote in message ... I need to handle the first find and delete a bit differently than the rest. After the first one, I want to repeatedly search from a few rows down from the first one until the end of the sheet, doing deletes. How do I stop the loop from continuing back to the start of the worksheet, finding text that I intentionally left behind. -------- Range("A1").Activate Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(-3, 0)).EntireRow.Delete Range("A1").Offset(4, 0).Activate Do Until Cells.Find("PAUL INC") Is Nothing Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(1, 0)).EntireRow.Delete Loop What is often done is to save the address of the starting cell and check each time to see if Find has returned to it. -- Bob Kilmer . I'm new to VBA, and am doing somewhat of a trial and error, looking in Help for examples. What would be the syntax of that save and comparison since I have the offset and delete with the cells.find and range? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I stop the find at the end of the sheet
See the 'FindNext Method' example in the VBE help.
HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- I need to handle the first find and delete a bit differently than the rest. After the first one, I want to repeatedly search from a few rows down from the first one until the end of the sheet, doing deletes. How do I stop the loop from continuing back to the start of the worksheet, finding text that I intentionally left behind. -------- Range("A1").Activate Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(-3, 0)).EntireRow.Delete Range("A1").Offset(4, 0).Activate Do Until Cells.Find("PAUL INC") Is Nothing Range(Cells.Find("PAUL INC").Offset(-2, 0), Cells.Find ("======").Offset(1, 0)).EntireRow.Delete Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find empty cell then stop | Excel Discussion (Misc queries) | |||
Stop and start Sheet recalculation | Excel Discussion (Misc queries) | |||
How can you make the Find function stop when it reached the end | Excel Discussion (Misc queries) | |||
VBA to stop XL from adding the Path Sring to Sheet-to-Sheet Links | Excel Discussion (Misc queries) | |||
How to stop Excel from doing wrong Find/Replace | Excel Discussion (Misc queries) |