Getting First Letter of Cell, Inserting a Row, Putting Letter above it

[GMC111568]

I have been searching, without luck, for a macro that will search a list that's already in alphabetical order (though maybe it wouldn't hurt to alphabetize it as part of the macro to be safe), and that will insert an empty row of each word that starts with the same letter, and put that letter in the first cell of the empty row capitalized and boldfaced. (I was able to find something that would insert a row above words that were the same in the list, I just can't figure out how to tweak that code so it only goes by the first letter rather than the entire word.

For example, I want a macro to turn this:

Acorns
Anchovies
Apples
Bananas
Blueberries
Chocolate
Cream
Crepes
Donuts
Fried Dough

into this:

A
Acorns
Anchovies
Apples

B
Bananas
Blueberries

C
Chocolate
Cream
Crepes

D
Donuts

F
Fried Dough


Thanks, everyone (this is my first post, but this site is awesome - thank you to everyone who has asked and answered questions - lots of great info here)!

 

[Rick Rothstein]

Assuming your list starts in cell A1 (no header), see if this macro does what you want...

[table="width: 500"]
[tr]
	[td]Sub PlaceLetterIndentifiers()
 Dim R As Long
 Application.ScreenUpdating = False
 Columns("A").Sort [A1], xlAscending
 For R = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
   If R = 1 Then
     Rows(1).Insert
     Cells(1, "A").Value = UCase(Left(Cells(2, "A").Value, 1))
     Cells(1, "A").Font.Bold = True
   ElseIf Left(Cells(R, "A").Value, 1) <> Left(Cells(R + 1, "A").Value, 1) Then
     Rows(R + 1).Resize(2).Insert
     Cells(R + 2, "A").Value = UCase(Left(Cells(R + 3, "A").Value, 1))
     Cells(R + 2, "A").Font.Bold = True
   End If
 Next
 Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[GMC111568]

PERFECT!!!

Thank you so much!!

 

[GMC111568]

One more quick question (I think) - how can I specify what sheet in the workbook I want this done on?

 

[Rick Rothstein]

One more quick question (I think) - how can I specify what sheet in the workbook I want this done on?

I figured the simplest thing would be for you to select the sheet before running the macro (the macro uses the active sheet) rather having to type the full name of the sheet in somewhere, but if you wanted to be able to specify the sheet... the decision is yours... you can have the code read the sheet name from a specified cell (you would need to tell me which sheet that cell was on and what the cell's address is) or the code could popup an InputBox where you could type in the sheet name directly. How did you want to proceed?

 

[GMC111568]

If there isn't a way to hard code it into the macro, then I guess maybe I'd have the code read it from a specified cell. My intent is to run a couple of different macros at once, to update different sheets in order to produce the final document.

Thanks for all of your help. I appreciate it.

 

[Rick Rothstein]

If there isn't a way to hard code it into the macro...

Yes, we can do that as well (change the red highlighted text to the name of the worksheet you want to process)...

[table="width: 500"]
[tr]
	[td]Sub PlaceLetterIndentifiers()
  Dim R As Long
  With Sheets("[B][COLOR="#FF0000"]Sheet3[/COLOR][/B]")
    Application.ScreenUpdating = False
    .Columns("A").Sort .Range("A1"), xlAscending
    For R = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
      If R = 1 Then
        .Rows(1).Insert
        .Cells(1, "A").Value = UCase(Left(.Cells(2, "A").Value, 1))
        .Cells(1, "A").Font.Bold = True
      ElseIf Left(.Cells(R, "A").Value, 1) <> Left(.Cells(R + 1, "A").Value, 1) Then
        .Rows(R + 1).Resize(2).Insert
        .Cells(R + 2, "A").Value = UCase(Left(.Cells(R + 3, "A").Value, 1))
        .Cells(R + 2, "A").Font.Bold = True
      End If
    Next
    Application.ScreenUpdating = True
  End With
End Sub[/td]
[/tr]
[/table]

 

[GMC111568]

Great - than you again. I've learned a lot from this code. I haven't done VBA stuff in many years, so I'm pretty rusty at it. Am enjoying learning it again.