Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Here is an example of what I am talking about.
A B C D 1 A B C D 2 2 41100 274 584 3 2 41100 274 625 4 2 41100 274 333 5 2 41100 274 102 6 2 41100 274 957 7 2 41100 274 362 8 2 41100 274 651 9 2 41100 274 611 10 2 41100 284 965 11 2 41100 284 336 12 2 41100 284 251 13 2 41100 284 362 14 2 41100 284 859 15 2 41100 284 512 16 2 41100 284 669 17 2 41100 284 512 I need to insert a new row every time the amount in Column "C" changes to a different amount than the previous one. There are approx 16,000 rows in this file or I would not be asking..lol Thanks for any help you can offer. -- Tony Johnson -- Tony Johnson |
#2
![]() |
|||
|
|||
![]()
Tony,
You can have a row insert automatically with the following code embedded as a change event for the sheet in question. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 3 Then Target.Rows.Insert End If Application.EnableEvents = True End Sub A better way to do this would be to have an add-in that activates a withevents class intercept of all Worksheet Change events. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Ronald "Tony" Johnson" wrote: Here is an example of what I am talking about. A B C D 1 A B C D 2 2 41100 274 584 3 2 41100 274 625 4 2 41100 274 333 5 2 41100 274 102 6 2 41100 274 957 7 2 41100 274 362 8 2 41100 274 651 9 2 41100 274 611 10 2 41100 284 965 11 2 41100 284 336 12 2 41100 284 251 13 2 41100 284 362 14 2 41100 284 859 15 2 41100 284 512 16 2 41100 284 669 17 2 41100 284 512 I need to insert a new row every time the amount in Column "C" changes to a different amount than the previous one. There are approx 16,000 rows in this file or I would not be asking..lol Thanks for any help you can offer. -- Tony Johnson -- Tony Johnson |
#3
![]() |
|||
|
|||
![]()
The below code works in an example I have - based on a change in Column A (in my example).
Adapt to your needs (Chg A to C).. Sub InsertRow_A_Chg() Dim Lrow As Long, vcurrent As String, i As Long '// find last used cell in Column A Lrow = Cells(Rows.Count, "A").End(xlUp).Row '// get the value of that cell in Column A (column 1) vcurrent = Cells(Lrow, 1).Value '// rows are inserted by looping from bottom up For i = Lrow To 2 Step -1 If Cells(i, 1).Value < vcurrent Then vcurrent = Cells(i, 1).Value Rows(i + 1).Resize(2).Insert 'Rows(i + 1).Insert to only Insert One Blank Row End If Next i End Sub Hope this helps, Jim May "Ronald "Tony" Johnson" wrote in message ... Here is an example of what I am talking about. A B C D 1 A B C D 2 2 41100 274 584 3 2 41100 274 625 4 2 41100 274 333 5 2 41100 274 102 6 2 41100 274 957 7 2 41100 274 362 8 2 41100 274 651 9 2 41100 274 611 10 2 41100 284 965 11 2 41100 284 336 12 2 41100 284 251 13 2 41100 284 362 14 2 41100 284 859 15 2 41100 284 512 16 2 41100 284 669 17 2 41100 284 512 I need to insert a new row every time the amount in Column "C" changes to a different amount than the previous one. There are approx 16,000 rows in this file or I would not be asking..lol Thanks for any help you can offer. -- Tony Johnson -- Tony Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
insert column move object | Excel Discussion (Misc queries) | |||
Formula for a column | Excel Discussion (Misc queries) | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
Auto Insert of Rows | Excel Worksheet Functions |