Try the below..(There is no error handling)

Public Sub Auto_DeleteRows()
Dim text1Row, text2Row As Long
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
text1Row = WS.Columns(1).Find("text1:", Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

text2Row = WS.Columns(1).Find("text2:", Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

WS.Rows(text1Row & ":" & text2Row).Delete
Next WS

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