Hi,

Am Mon, 1 Feb 2021 01:31:17 -0800 (PST) schrieb Tatsujin:

I have a column of data similar to this:

ant
antique
art
bee
beautiful
bored
chores
dancing
daytime

Does Excel have any means of finding the rows where the first letter of the alphabet changes to a new letter, and insert a new row in between such as this:

----A----
ant
antique
art
----B----
bee
beautiful
bored
----C----
chores
----D---
dancing
daytime

etc. etc... I'm pretty much trying to insert letter headers above the start of each new letter series.

if your values are in column A then try:

Sub Test()
Dim LRow As Long, i As Long
Dim myStr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
If Left(.Cells(i, 1), 1) < Left(.Cells(i - 1, 1), 1) Then
myStr = "---" & UCase(Left(.Cells(i, 1), 1)) & "---"
.Rows(i).Insert
.Cells(i, 1) = myStr
End If
Next
.Rows(1).Insert
.Cells(1, 1) = "---" & UCase(Left(.Cells(2, 1), 1)) & "---"
End With
End Sub

Otherwise change the references.


Regards
Claus B.
--
Windows10
Microsoft 365 for business