| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy, Paste then delete
Hello
I have a data sheet that has sorted values in ColA:D. For each line on the list I would like to compare the values in ColB and ColD to the values in the next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and then delete Row2. Example Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Would become: Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Thanks for all replies Louise |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy, Paste then delete
This works for me. Save your data before testing.
Sub MoveData() Range("A2").Select Do Until ActiveCell.Value = Empty If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _ And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1, 3).Value Then ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value ActiveCell.Offset(1, 0).EntireRow.Delete ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End Sub Regards Rowan "Louise" wrote: Hello I have a data sheet that has sorted values in ColA:D. For each line on the list I would like to compare the values in ColB and ColD to the values in the next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and then delete Row2. Example Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Would become: Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Thanks for all replies Louise |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy, Paste then delete
Lookout for the wrapping of line 4. Should be more like
Sub MoveData() Range("A2").Select Do Until ActiveCell.Value = Empty If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _ And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1, 3).Value Then ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value ActiveCell.Offset(1, 0).EntireRow.Delete ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End Sub Regards Rowan "Rowan" wrote: This works for me. Save your data before testing. Sub MoveData() Range("A2").Select Do Until ActiveCell.Value = Empty If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _ And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1, 3).Value Then ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value ActiveCell.Offset(1, 0).EntireRow.Delete ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End Sub Regards Rowan "Louise" wrote: Hello I have a data sheet that has sorted values in ColA:D. For each line on the list I would like to compare the values in ColB and ColD to the values in the next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and then delete Row2. Example Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Would become: Dog 123456 Blue 500 Dog 123456 Red 400 Dog 234123 Blue 500 Dog 234123 Blue 500 Thanks for all replies Louise |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| check box, copy / paste or insert / delete row | Excel Worksheet Functions | |||
| subtotal copy-paste and delete hidden rows | Excel Discussion (Misc queries) | |||
| copy paste delete cell contents | Excel Discussion (Misc queries) | |||
| Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
| Protect chartobjects, allow copy paste but NOT CUT (or delete) | Excel Programming | |||
Hybrid Mode
