VBA – Letter Count for Each Alphabet Character – Excel Macro

/in , , /by

This Excel Macro will go through all the cells in column A and output the count for each letter in column B. This is for English alphabet only and it will skip all the other characters and spaces.

 

Sub LetterCount()

Dim ws As Worksheet
Set ws = ActiveSheet

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

rg = Range("a1:a" & lastrow)
i = 1

For c = 97 To 122

cnt = 0

    For Each cell In rg

    lth = Len(cell)
        For lt = 1 To lth

            chktext = Mid(cell, lt, 1)

              If chktext = Chr(c) Then

                cnt = cnt + 1
            End If

        Next lt

    Next cell

    If cnt > 0 Then
        ws.Cells(i, 2) = Chr(c) & " = " & cnt
        i = i + 1
    End If

Next c

End Sub

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/