new

[De]

Insert row at each change of surname in column

 

[Don Guillett]

Sub insertrowifnamechg()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) <> Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

 

[Stephen]

Subtotal

 

[De]

I can't see how I can insert a row using subtotals; the only option seems to
be page break. Is there a way to insert a row? Many thanks,

 

[Stephen]

I assumed you wanted to do something with the inserted rows, so I was
thinking of
Data > Subtotals
which allows you to specify what you want to do on the inserted rows (sum,
average, etc.)

 

[De]

Showing my ignorance but is this a macro? Because it doesn't look like it
will go on the formula bar.

 

[De]

Many thanks for this but is is a macro? I tried it as both a macro and in
the formula bar but I'm not confident with macros so I might not have got it
right.

 

[Don Guillett]

A formula could not insert a row. This is a macro
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[De]

Many tks for the help.
--
De

Sub insertrowifnamechg()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) <> Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)