Microsoft Office Forums - View Single Post - [Solved] Have an alphbetical list need to count how many names start with A, B, C etc.

jolivanes · 09-02-2014, 02:06 PM

Here is another possibility without the need for a Help Column.
In this code it assumes that Column C (3) is free. Change as required.

Code:

Sub One_More_Way()
    Dim j As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row    '<---- Column A (1) is being checked. Change as required
    For j = 1 To 26
        'The number 3 in Cells(j, 3) in the next line is Column C (3rd Column).Change as required.
        Cells(j, 3).Value = Application.WorksheetFunction.CountIf(Range("B1:B" & lr), _
        Chr(64 + j) & "*") & " x " & Chr(64 + j) & "'s"
    Next j
End Sub