Prev: How do I change the comment default format to include date?
Next: Counting a Column
From: Russell Dawson on 10 May 2010 10:26
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.
From: Don Guillett on 10 May 2010 11:10
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
dguillett(a)gmail.com
"Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in message
news:38C517CC-97A3-443B-8699-254604DEBFCF(a)microsoft.com...
> 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.

From: Dave Peterson on 10 May 2010 11:13
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
From: Mike H on 10 May 2010 11:21

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.
From: Don Guillett on 10 May 2010 11:22
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
dguillett(a)gmail.com
"Don Guillett" <dguillett1(a)gmail.com> wrote in message
news:e14tsLF8KHA.2292(a)TK2MSFTNGP04.phx.gbl...
> 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
> dguillett(a)gmail.com
> "Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in
> message news:38C517CC-97A3-443B-8699-254604DEBFCF(a)microsoft.com...
>> 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.
>

 |  Next  |  Last
Pages: 1 2
Prev: How do I change the comment default format to include date?
Next: Counting a Column