Sub CountLetters()
''a count of each letter in a range outputted to a new sheet
Dim letCount(1 To 26) As Long
Dim wkSht As Worksheet
Dim ii As Long
Dim Cell As Range
Dim WrkRng As Range
For Each wkSht In Worksheets
With wkSht
If .Name = "ListLetters" Then
Application.DisplayAlerts = False
Sheets("ListLetters").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set WrkRng = ActiveSheet.UsedRange
For Each Cell In WrkRng
For ii = 1 To Len(Cell)
If Mid(UCase(Cell), ii, 1) Like "[A-Z]" Then
letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) = _
letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) + 1
End If
Next ii
Next Cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(26, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(26, 1)
.Formula = "=char(row()+64)"
.Value = .Value
End With
End Sub
Gord Dibben MS Excel MVP
