Search for the word "continued", if found, delete that row + 10 rows above

[jriendeau5]

Hi There

I need help with the following macro (VBA). I need to look at a file i
Excel and search for the word "continued". If it is found, I need t
delete that row and 10 rows above it. Can someone help me with this?

Thanks

Jenny :

 

[Dave Peterson]

I think this works ok:

Option Explicit

Sub testme()

Dim myWord As String
Dim RowsToDelete As Long
Dim FoundCell As Range

myWord = "continued"
RowsToDelete = 11

With ActiveSheet
Do
Set FoundCell = .Cells.Find(What:=myWord, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Row > RowsToDelete Then
FoundCell.Offset(-RowsToDelete +
1).Resize(RowsToDelete).EntireRow.Delete
Else
.Range("A1:A" & FoundCell.Row).EntireRow.Delete
End If
Loop
End With
End Sub

Make sure you're ok with the .find parms (xlvalues, xlpart, and matchcase).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm