loop to delete certain text and stop if cannot find

[Mhabashy]

Hello every one
i am still new at Macro and i am trying to do a code to search for text "BOX" and delete the cells which is many at the columns, shifting up after deleting and when finish stop, but actually after deleting the cells I am facing error number '91'
can any one help please.BOX.jpg

my code is

Sub Test3()

For i = 3 To 10000
If Cells.Find(What:="BOX").Select Then
Selection.Delete Shift:=xlUp
End If
Next

End Sub

[rollis13]

When deleting it's easier if you start bottom up:

Please Login or Register  to view this content.

[Mhabashy]

thanks for your notice but still the same error.

[rollis13]

I know, you need another approach, this will do what you are going for:

Please Login or Register  to view this content.

[Artik]

It will delete only those rows that contain "BOX" in column A.
If "BOX" occurs in other columns, they will not be deleted.

Please Login or Register  to view this content.

The loop

Please Login or Register  to view this content.

will execute unnecessarily 9997 times even if all cells containing "BOX" have already been deleted.

Artik

[Mhabashy]

thanks very much its working - more over i want to delete the "BOX" row and (two rows above it) and (3 rows under it) thanks for your patient
before iam using some think like the following code but now i cannot adjust with your formula



Dim StartRange As String
Dim EndRange As String
For i = 3 To 10000
If Cells.Find(What:="BOX").Select Then
Selection.Offset(-2, 0).Select
StartRange = ActiveCell.Address
Selection.Offset(5, 0).Select
EndRange = ActiveCell.Address
ActiveSheet.Range(StartRange & ":" & EndRange).Select
Selection.Delete Shift:=xlUp
End If
Next

End Sub

[Artik]

If "BOX" occurred in row 4, what should be done? Still delete rows 2 and 3, or should they be protected?

Artik

[Mhabashy]

no need to be removed
please see the attached picture you will get what i want
All the highlighted gray rows need to be removed

Attachment 785448

[Artik]

The image is not visible. Attach it according to the yellow banner at the top of this page.

Artik

[Mhabashy]

sorry i think this one will come box 2.jpg

[Artik]

Please Login or Register  to view this content.

Artik

[Mhabashy]

Sorry but nothing happened please find attached excel sample

[Artik]

Remove this code fragment:

Please Login or Register  to view this content.

Artik

[Mhabashy]

thank a lot -- its working finally , i am really appreciating your efforts.

thank you

[Artik]

The whole thread should have been started by posting a workbook, not pictures, which in addition did not reflect the actual layout of the data. We unnecessarily beat the foam for several posts
If your problem has been solved, please mark the thread as SOLVED.

Artik

[Mhabashy]

thanks again for your help (Solved)