Hi Again Andy,

Matt Herberts comments are very valid. Good pickup Matt. I often keep code
to the minimum to give the OP the idea and leave it to the OP to expand on
it. However, as you have raised the issue, I will answer it and improve the
code a little.

It is bad programming to use ActiveCell even though it is used when code is
recorded. I have this habit of recording a line of code for Find in lieu of
trying to remember all of the parameters. The recorded code needs a little
editing after recording it and I simply forgot to remove the reference to
ActiveCell. In this case it did not matter because the search range is the
entire worksheet but if it was restricted to a specific range then the
ActiveCell might not be within the search range and then the code errors out.

Yes it is a good programming to set After as the last cell in the range to
search. The reason to set it as after the last cell is that if the first cell
happens to meet the find criteria then it is the Next cell after the first
cell to meet the criteria that is found. However, if the first cell is the
only cell meeting the criteria then it will be found because the Find loops
around once from the last cell to the first if there are no other instances
of the criteria.

NOT testing for the Find results is also NOT good programming. The code will
error out when trying to use the results of the Find if it is Nothing.

Code below is now set to the last cell of the used range for Find "OTHER
CHARGES" meaning that it will always find the first instance meeting the
criteria and then After "OTHER CHARGES" for the second find. Also tests the
results of each Find before attempting to use the results of the Find. Note
that the result of the first find is tested before it is used as the After
parameter in the second Find.

Of course as Matt pointed out, if you have multiple instances of 'OTHER
CHARGES' and 'KM In' on the same worksheet then will need more work on the
code to loop through all instances.

Sub Delete_Rows()

Dim rngStartCell As Range
Dim rngLastCell As Range
Dim lngRowStart As Long
Dim lngRowLast As Long
Dim strToFindStart As String
Dim strToFindLast As String
Dim rngLastUsed As Range

strToFindStart = "OTHER CHARGES"
strToFindLast = "KM In"

'Edit "Sheet1" in the following row _
to match your sheet name
With Sheets("Sheet1")

'Assign the last used cell on the worksheet to a range variable
Set rngLastUsed = .Cells(.UsedRange.Rows.Count, _
.UsedRange.Columns.Count)

Set rngStartCell = .Cells.Find _
(What:=strToFindStart, _
After:=rngLastUsed, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If rngStartCell Is Nothing Then
MsgBox "Did not find " & strToFindStart & vbCrLf & _
"Processing terminated"
Exit Sub
End If

Set rngLastCell = .Cells.Find _
(What:=strToFindLast, _
After:=rngStartCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If rngLastCell Is Nothing Then
MsgBox "Did not find " & strToFindLast & vbCrLf & _
"Processing terminated"
Exit Sub
Else
'Test if rows exist between first and last identifiers
If rngLastCell.Row - rngStartCell.Row 1 Then
lngRowStart = rngStartCell.Row + 1
lngRowLast = rngLastCell.Row - 1
Else
MsgBox "No rows to delete between " & _
rngStartCell & " and " & rngLastCell
Exit Sub
End If
End If

.Rows(lngRowStart & ":" & lngRowLast).Delete Shift:=xlUp

End With

End Sub


--
Regards,

OssieMac