Search for sepcific words and delete rows between them

[Sheela]

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

 

[Jacob Skaria]

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]

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

 

[Gary''s Student]

Sub dural()

' put the following in the Worksheet loop

Dim text1Row As Range, text2Row As Range
Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)
Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)
Dim s As String
s = text1Row.Row & ":" & text2Row.Row
Rows(s).Delete

End Sub

 

[Sheela]

It worked. Thank you so much to both of you for quick responses.

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

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

 

[Rick Rothstein]

I have a couple of questions for clarification (with an eye toward error
handling)...

Will "text1" ALWAYS appear at a lower row number than "text2"? If not, and
"text2" occurs before "text1", do you still want perform your deletion?

Can "text1" and/or "text2" appear more than once in the list? If so, which
ones do you want to use... the lower numbered row where "text1" appears and
the higher numbered row where "text2" appears, or some other way?

 

[Sheela]

Thank you for your concern.

The "text1" always appears before "text2". And it can also comes with other
words after text2.

I realized this after posting my question and I changed the search criteria
for text1 such that the direction should be xlnext.

Text2 comes only once in a worksheet.

Thank you for the heads up.

I have a couple of questions for clarification (with an eye toward error
handling)...

Will "text1" ALWAYS appear at a lower row number than "text2"? If not, and
"text2" occurs before "text1", do you still want perform your deletion?

Can "text1" and/or "text2" appear more than once in the list? If so, which
ones do you want to use... the lower numbered row where "text1" appears and
the higher numbered row where "text2" appears, or some other way?

--
Rick (MVP - Excel)

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