Insert rows when data changes

In a column of periodically changing names in alphabetical order (col A) – I
need to insert a row after each change in initial letter. So when the names
have been sorted when the initial letter of the name changes from A to B as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the names
first rather than that being a separate action. Sort by column A to include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Sub Sort_InsertRowsSAS()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lr).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
For i = lr To 2 Step -1
If Cells(i - 1, 1) Cells(i, 1) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Russell Dawson" wrote in message
...
In a column of periodically changing names in alphabetical order (col A) –
I
need to insert a row after each change in initial letter. So when the
names
have been sorted when the initial letter of the name changes from A to B
as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the
names
first rather than that being a separate action. Sort by column A to
include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

I'd add an additional column (a new column A) with a formula like:

=left(b2,1)
and fill down.

Then I could use data|subtotals to group by this column.

If I wanted it reproduced mechanically, I'd record a macro when:
I removed data|subtotals (Remove all)
Sorted the data by column A (ascending) and column B (ascending)
Reapplied data|subtotals



Russell Dawson wrote:
In a column of periodically changing names in alphabetical order (col A) – I
need to insert a row after each change in initial letter. So when the names
have been sorted when the initial letter of the name changes from A to B as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the names
first rather than that being a separate action. Sort by column A to include
other related data in columns to F.

Thanks

--

Dave Peterson

Russell,

Try this macro which does the sort and insert the rows. Change SHT tp your
sheet

Sub insertrowifnamechg()
MyColumn = "A"
Set sht = Sheets("Sheet1")
With sht
'Sort data
..Columns("A:F").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Insert rows
For x = .Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
If Left(.Cells(x - 1, MyColumn), 1) _
Left(.Cells(x, MyColumn), 1) Then .Rows(x).Insert
Next x
End With
End Sub




--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Russell Dawson" wrote:

In a column of periodically changing names in alphabetical order (col A) – I
need to insert a row after each change in initial letter. So when the names
have been sorted when the initial letter of the name changes from A to B as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the names
first rather than that being a separate action. Sort by column A to include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

should have been

If left(Cells(i - 1, 1),1) left(Cells(i, 1),1) Then Rows(i).Insert

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub Sort_InsertRowsSAS()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lr).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
For i = lr To 2 Step -1
If Cells(i - 1, 1) Cells(i, 1) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Russell Dawson" wrote in
message ...
In a column of periodically changing names in alphabetical order (col
A) – I
need to insert a row after each change in initial letter. So when the
names
have been sorted when the initial letter of the name changes from A to B
as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the
names
first rather than that being a separate action. Sort by column A to
include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Many thanks for the answers.


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Russell Dawson" wrote:

In a column of periodically changing names in alphabetical order (col A) – I
need to insert a row after each change in initial letter. So when the names
have been sorted when the initial letter of the name changes from A to B as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the names
first rather than that being a separate action. Sort by column A to include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

The archives want to see your final answer

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Russell Dawson" wrote in message
...
Many thanks for the answers.


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Russell Dawson" wrote:

In a column of periodically changing names in alphabetical order (col
A) – I
need to insert a row after each change in initial letter. So when the
names
have been sorted when the initial letter of the name changes from A to B
as
in Aldred to Bute there is a blank row inserted after Aldred.

It would be nice if the code could include the instruction to sort the
names
first rather than that being a separate action. Sort by column A to
include
other related data in columns to F.

Thanks
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.