There many be times when you need to know how many words start with a given letter. You can easily accomplish this with VBA. We created a VBA macro that lets you do this.
The macro that we developed to do this is shown below.
The Macro that Lets You Count How Many Words Start with a Given Letter
To use this letter, you need to copy and paste the list of words in Column A. Then you need to show the letter that you want to count in column B.
You will need to run the macro by copying it into the Visual Basic editor, which can be found under the Developer tab. You will need to click Insert Module first. Then, click the run button or push F11.
Sub CountCellsStartingWithLetter()
Dim ws As Worksheet
Dim lastRow As Long
Dim letter As String
Dim count As Integer
Dim cell As Range
‘ Set the worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Change “Sheet1” to your sheet name
‘ Find the last row with data in column B
lastRow = ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row
‘ Prompt user to enter the letter
letter = InputBox(“Enter the letter to count cells starting with:”, “Letter Input”)
‘ Validate input
If Len(letter) <> 1 Then
MsgBox “Please enter only one letter.”, vbExclamation
Exit Sub
End If
‘ Convert letter to uppercase
letter = UCase(letter)
‘ Reset count
count = 0
‘ Loop through each cell in column B
For Each cell In ws.Range(“B1:B” & lastRow)
‘ Check if the cell starts with the specified letter
If UCase(Left(cell.Value, 1)) = letter Then
count = count + 1
End If
Next cell
‘ Display the count
MsgBox “Number of cells starting with ‘” & letter & “‘: ” & count, vbInformation
End Sub
Can You Do this Without VBA?
You can still count the number of words starting with a given letter without using VBA. However, you will need to do so in a more roundabout way. The best way to do this is by sorting the column and then copying and pasting all words that you see with the given letter into the first cell in another column. Then, you can see how many rows are filled. You will have to use the undo command or clear the column after.
This will take less time if you are just doing it once. However, you may want to use VBA if you plan on counting cells like this more frequently.
We hope this helps!