find word initial then final......

[amorrison2006]

Can someone please help with this small request.

I need a macro to find the word "intial" in column A and if the word
in the cell immediately below that contains the word "final" then it
should delete both the two rows.

The have to be in the order of "Intial" then "Final" as this would
mean there has been no activity in between the two.

I hope someone can help.

Thanks in advance,

Andrea

 

[David]

Try this, by no means the most efficient way but it seems to work. I
recommend you test it on some backed-up files first as I am not going
to be held liable if it does something it shouldn't! Change the range
"A1:A1000" to however many rows you need.

Sub find()
RowNum = 0
For Each cell In Range("A1:A1000")
RowNum = RowNum + 1
If cell.Value = "Initial" Then
If cell.Offset(1, 0).Value = "Final" Then
Rows(RowNum).EntireRow.Delete
Rows(RowNum + 1).EntireRow.Delete
End If
End If
Next
End Sub

 

[Guest]

Sub test()

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = Lastrow To 1 Step -1
If (Range("A" & RowCount) = "Initial") And _
Range("A" & RowCount + 1) = "Final" Then

Rows(RowCount & ":" & (RowCount + 1)).Delete
End If
Next RowCount


End Sub

 

[amorrison2006]

Thank you so much

This worked perfectly.

Andrea

 

[Dave Peterson]

Check your other post.

Can someone please help with this small request.

I need a macro to find the word "intial" in column A and if the word
in the cell immediately below that contains the word "final" then it
should delete both the two rows.

The have to be in the order of "Intial" then "Final" as this would
mean there has been no activity in between the two.

I hope someone can help.

Thanks in advance,

Andrea