Auto 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,

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


"Ronald "Tony" Johnson" <[email protected]> wrote in message
news:O6%[email protected]...
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

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

On Tue, 3 May 2005 21:42:42 -0400, "Ronald \"Tony\" Johnson"
<[email protected]> 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.