hey all

I want to find a value in a worksheet and then delete 5 lines under every occurance of the value.

I have been toying with:

For Count = 1 To x

Range("A1").Select
Cells.Find(What:="Report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Delete Shift:=xlUp

Next Count
End Sub

How do i find for x

Or if I use a do while loop how do i set the While condition?

Can someone please tell me how this can be achieved.
thanks

Hi

From what I could gather; in "For Count = 1 To x" x would be the number of times you expect to find the word "Report" If your code does not find the word it will give you a runtime error. So I would think that its save to assume you could loop till you get an error. What I would have done is add a On Error GOTO ERR: and the on your ERR: part exit the sub. So you could set x to any big number.

I would suggest this as a solution if you only trying to sort out one or two workbooks data but not for a module you would want to distribute. Let me know of the requirement and maybe I could come up with a better solution than this! :)

Regards
Marnus

hey acdsky

thanks for your help. Went with your suggestion, however i would also like to know how a more "sophisticated" approach would handle it.

I would have thought that a DO While Loop would be perfect for this task. ie do while Cells.Find(What:="Report",... is not false/null

FYI - Someone on another forum came up with this solution, but i couldnt get it to work.

Sub AAA()
Dim oCell As Range
Dim sAddr As String
Set oCell = Range("IV65536")
Do
Set oCell = Cells.Find(What:="Report", _
After:=oCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Debug.Print oCell.Address
If Not oCell Is Nothing Then
If oCell.Address = sAddr Then Exit Do
If sAddr = "" Then sAddr = oCell.Address
oCell.Offset(1, 0).Resize(5, 1).EntireRow.Delete Shift:=xlUp
End If
Loop While Not oCell Is Nothing
End Sub

Once again thanks for your help