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

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

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

WOW, thanks for all your help OssieMac, I will try it when I get home from
work but I am sure it will work great. You are a star.

Thanks again
Regards
Andy




"OssieMac" wrote in message
...
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

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

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