I would code this the below way..

Public Sub Auto_DeleteRows1()
Dim rngFind1 As Range, rngFind2 As Range, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Set rngFind1 = ws.Columns(1).Find("text1:", Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set rngFind2 = ws.Columns(1).Find("text2:", Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not (rngFind1 Is Nothing) And Not (rngFind2 Is Nothing) Then _
ws.Rows(rngFind1.Row & ":" & rngFind2.Row).Delete
Next

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Sheela" wrote:

This question must have been answered before, but I am not able to figure out
the cause to generate the error. sometimes it is working but sometimes it is
giving error.

I am trying to run a macro, to search for two specific words and delete
entire rows between these words, including them.
These searching words are always in column 1.

I am using the following code: but it is giving runtime error 91 (Object
variable or with block variable not set). How can I correct this. Thank you
so much in advance for any help.


#############

Public Sub Auto_DeleteRows()


Dim text1Row , text2Row As Long

Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets

With WS

.Activate

Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)

Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)


' delete all rows between text1 and text2:


Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete

End With

Next WS

End Sub