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

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 :

--
jriendeau
-----------------------------------------------------------------------
jriendeau5's Profile: http://www.excelforum.com/member.php...fo&userid=1608
View this thread: http://www.excelforum.com/showthread.php?threadid=27534

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

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


jriendeau5 wrote:

Hi There

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

Thanks

Jenny :)

--
jriendeau5
------------------------------------------------------------------------
jriendeau5's Profile: http://www.excelforum.com/member.php...o&userid=16081
View this thread: http://www.excelforum.com/showthread...hreadid=275343

--

Dave Peterson