Search for sepcific words and delete rows between them
It worked. Thank you so much to both of you for quick responses.
"Jacob Skaria" wrote:
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
|