Thanks for the additional help Matt.

I think you just got us the wrong way around, OssieMac was the helper who
provided the code and I myself, Andy was the helped!!

Thanks a lot for replying, you are a gent.

Cheers!
Andy


wrote in message
...
On Mar 21, 11:05 pm, OssieMac
wrote:
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- Hide quoted text -

- Show quoted text -

OssieMac,

Andy gave you some great code to work with. As an add-on to what Andy
mentioned, consider your search strings. The program assumes you have
"KM In" and "OTHER CHARGES" and that each of these exist only once in
the spreadsheet. If this is not the case, then you might consider
revising the After argument of "Set rngStartCell = .Cells.Find After:=
" and "Set rngLastCell = .Cells.Find After:=" to be the last cell in
the Find range rather than the ActiveCell in the Find range. (This
will help in setting something up for a situation wherein your
spreadsheet may have more than one "KM In" or "OTHER CHARGES" in it
and you can track when Find has looped through the cells one cycle).
Also, you'll want to test if rngStartCell and/or rngLastCell is
nothing. However, it's likely that you are running this macro only on
a spreadsheet specifically designed with "KM In" and "OTHER CHARGES"
so this may not be necessary. But it may prove useful for something
else in the future.

Best,

Matt Herbert