what I want to do is to step through the records which are sorted by a key
field, then a subkey. I want to combine the info part number for all that
have the same key in to the last record of the key, then mark the other
records to be deleted.

I have not done any programming in Excel, and what programming I have done
was wayback in dBase3 days.

conceptually, this is what I have come up with, but I am not sure how to do
this in VB. And I am not sure if this is the best approach.



function cleanparts

i=row()
x = column()
testvalue= value of cell(ix)
vString =""

do while cell(row(i),column(x)) = testvalue
cell(row(i), column(x - 4)) = "marked for deletion"
vString = cell(row(i), column(x - 3)) & ", " & vstring
i=i+1
enddo

'last row, do not mark for deletion
cell(row(i), column(x - 4)) = " keep"

return vString

Thanks!
Mc

I hope thjs is close to what you want. We can make adjustments if needed.
did the best I could from you program. I uses the active cell as the
starting position. there is no return value so I made it a sub rather than a
function. the sub will delete the cells as it moves down the rows with the
same key.


Sub cleanparts()

MyRow = ActiveCell.Row
MyColumn = ActiveCell.Column
Testvalue = ActiveCell.Value

vstring = ""
Do While StrComp(Cells(MyRow + 1, MyColumn), Testvalue) = 0

vstring = vstring & Cells(MyRow, MyColumn - 3).Value
Cells(MyRow, MyColumn).EntireRow.Delete
Loop
vstring = vstring & Cells(MyRow, MyColumn - 3).Value
Cells(MyRow, MyColumn - 3).Value = vstring
End Sub
"Mctabish" wrote:

what I want to do is to step through the records which are sorted by a key
field, then a subkey. I want to combine the info part number for all that
have the same key in to the last record of the key, then mark the other
records to be deleted.

I have not done any programming in Excel, and what programming I have done
was wayback in dBase3 days.

conceptually, this is what I have come up with, but I am not sure how to do
this in VB. And I am not sure if this is the best approach.



function cleanparts

i=row()
x = column()
testvalue= value of cell(ix)
vString =""

do while cell(row(i),column(x)) = testvalue
cell(row(i), column(x - 4)) = "marked for deletion"
vString = cell(row(i), column(x - 3)) & ", " & vstring
i=i+1
enddo

'last row, do not mark for deletion
cell(row(i), column(x - 4)) = " keep"

return vString

Thanks!
Mc

Joel,
Tha k you much.
I do not want to delete the rows right now, as I need to also step through
with 3 more aspects of this routine. So right now, I just want to mark it,,
then I will test again for condition2, and then for the 3rd time. Then all
that meet all tree would get deleted. Thanks again though!

Mc

"Joel" wrote in message
...
I hope thjs is close to what you want. We can make adjustments if needed.
did the best I could from you program. I uses the active cell as the
starting position. there is no return value so I made it a sub rather
than a
function. the sub will delete the cells as it moves down the rows with
the
same key.


Sub cleanparts()

MyRow = ActiveCell.Row
MyColumn = ActiveCell.Column
Testvalue = ActiveCell.Value

vstring = ""
Do While StrComp(Cells(MyRow + 1, MyColumn), Testvalue) = 0

vstring = vstring & Cells(MyRow, MyColumn - 3).Value
Cells(MyRow, MyColumn).EntireRow.Delete
Loop
vstring = vstring & Cells(MyRow, MyColumn - 3).Value
Cells(MyRow, MyColumn - 3).Value = vstring
End Sub
"Mctabish" wrote:

what I want to do is to step through the records which are sorted by a
key
field, then a subkey. I want to combine the info part number for all that
have the same key in to the last record of the key, then mark the other
records to be deleted.

I have not done any programming in Excel, and what programming I have
done
was wayback in dBase3 days.

conceptually, this is what I have come up with, but I am not sure how to
do
this in VB. And I am not sure if this is the best approach.



function cleanparts

i=row()
x = column()
testvalue= value of cell(ix)
vString =""

do while cell(row(i),column(x)) = testvalue
cell(row(i), column(x - 4)) = "marked for deletion"
vString = cell(row(i), column(x - 3)) & ", " & vstring
i=i+1
enddo

'last row, do not mark for deletion
cell(row(i), column(x - 4)) = " keep"

return vString

Thanks!
Mc