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" wrote:

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.