Insert Row When Column Amount Changes
 

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

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

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