Auto Insert Row When Column Amount Changes

[Ronald \Tony\ Johnson]

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.

 

[Jim Cone]

Tony,

One way...
Use Data | Subtotals on column C.
Collapse the displayed subtotals so only the sub-totaled
and Grand total rows are showing.
Use Edit | GoTo | Special and select the visible cells.
Delete them and expand the sub-total display.

Regards,
Jim Cone
San Francisco, USA


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

 

[Gord Dibben]

Macro solution OK?

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 3) <> Cells(i, 3) Then _
Cells(i, 3).Resize(1, 3).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben Excel MVP