Hi again Andy,

I am viewing this thread on the Microsoft Communities web site. A lot of the
news groups crosss post. Microsoft do not support attachments so I cannot see
your screen picture. However, I think that I get the idea of what you want to
do. That is identify the text 'OTHER CHARGES' and the text 'KM In' and delete
all the rows between but leave the rows with the text 'OTHER CHARGES' and 'KM
In'. The following should do it for you.

Because the macro deletes rows, ensure that you backup your workbook before
testing and also test extensively to ensure that it does what you want.

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

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

'Edit "Sheet1" in the following row _
to match your sheet name
With Sheets("Sheet1")
Set rngStartCell = .Cells.Find _
(What:=strToFindStart, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

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

'Test if any rows 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 start and KM In"
Exit Sub
End If

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

End With

End Sub

--
Regards,

OssieMac


"Andy Rigby" wrote:

Hi OssieMac

Thanks for the reply.

Sorry to be so vague before, basically I have a macro that formats the
worksheet, turns text to columns in various places and parses out any
leading spaces. A JPG of a section of the worksheet is shown attached, this
is what the sheet looks like after the initial macro has been run. The
selected cell after the routine has run is A26 which is on the words
"VEHICLE CLASS". The next red shaded rows which start "VCHR#" need to stay
in the sheet but unfortunately there are sometimes more than two rows of
data here and more often only one row but they will always begin with
"VCHR#" The rows that require deletion are the row which has the text "OTHER
CHARGES....." and then all rows with text under that row (all the red shaded
rows) until it reaches "KM In...." - this row and all others after this must
remain in the sheet. So red shaded rows are to stay and green shaded rows
are to go!! Don't worry about the figures which do not calculate correctly,
I can fix that later!

I hope this makes sense, many thanks for any help you can offer.





"OssieMac" wrote in message
...
Hi Andy,

Can you post the code for finding the starting cell? Need to determine
what
method to use to identify the row number.

I am assuming that you want to delete all rows from one row after the
starting cell to one row before the row containing "KM In". Is this
assumption correct?

--
Regards,

OssieMac


"Andy Rigby" wrote:

Hi All

Sorry I am fairly new to programming Excel. I am currently using Excel
2003.
I need to use macro/vba code to, delete rows from a worksheet (these rows
may or may not contain data or text strings) and I need it to stop at a
row
that contains certain data (the text string "KM In"). I don't have a
particular range I can use in a macro as each worksheet this routine will
be
applied to has different numbers of rows that need removing, but there
will
be a starting cell which I have already worked out how to get to.

So basically from that starting cell I need to remove the following rows
until a row containing the text string "KM In" is reached and then the
routine needs to stop.

Any help or suggestions would be gratefully received.

Kind regards
Andy
Cairns, Australia