insert a row when theres change in a row.

[elaine]

Hi all,

Does anyone knows how to insert a row automatically when the value
within a column changes?

Ie. Column A is list of names, sort by A to Z, (where there are values
in other columns too)
So you may have a column looks like:

Amanda
Amanda
Amanda
Becca
Becca
Charlotte
Elle
Elle
Elle etc

Is there a way to automatically insert a row after changes to make the
following:

Amanda
Amanda
Amanda

Becca
Becca

Charlotte

Elle
Elle
Elle

Thanks.
Elaine.

 

[Guest]

Paste this into a standard module:


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(1).Insert 'NUMBER of Rows to Insert Line
-->>Rows(i + 1).Insert to only Insert One Blank Row
End If
Next i
End Sub

Hope it helps,

Jim May

 

[elaine]

Hi JM,

does it mean that I could choose any column buy change 'A' to what
ever column its on and the code will still work?


Thanks
E.

 

[Guest]

Yeah, Say you want Column D

Sub InsertRow_D_Chg()
Dim Lrow As Long, vcurrent As String, i As Long
'// find last used cell in Column D
Lrow = Cells(Rows.Count, "D").End(xlUp).Row
'// get the value of that cell in Column D (column 1)
vcurrent = Cells(Lrow, 4).Value
'// rows are inserted by looping from bottom up
For i = Lrow To 2 Step -1
If Cells(i, 4).Value <> vcurrent Then
vcurrent = Cells(i, 4).Value
Rows(i + 1).Resize(1).Insert 'NUMBER of Rows to Insert Line
'-->>Rows(i + 1).Insert to only Insert One Blank Row
End If
Next i
End Sub

 

[Dave Peterson]

You may want to add headers, sort your data (if you want) and then use
data|subtotals to get that inserted line.

And you'll end up with counts or sums of anything field you want, too. Along
with the outlining symbols to the left that allow you to hide/show details.

 

[elaine]

It works perfectly if I only have a few rows of data, but it seems to
take a long time/crashing when I use this code on a 30,000 rows
data.... How long does it take the code to work for 30k rows???

 

[Dave Peterson]

It takes a long time!

Try turning calculation to manual (tools|Option|calculation tab) to see if that
helps.