Studies show that good grammar leads to more promotions and higher salaries. Write with Grammarly

Count Words in Spreadsheet in Excel VBA

In this post, you’ll learn how to count words in your excel spreadsheet using Excel VBA.

Count Words in Spreadsheet in Excel VBA

To count words in a spreadsheet,

First, insert a command button then add the following codes:

Code:

Private Sub CommandButton1_Click()

Dim sheet As Range, cell As Range

Dim txtinCell, wordsinRange As Integer, text As String

Set sheet = Selection

txtinCell = 0

wordsinRange = 0

For Each cell In sheet

    If Not cell.HasFormula Then

        text = cell.Value

        text = Trim(text)

        If text = "" Then

            txtinCell = 0

        Else

            txtinCell = 1

        End If

        Do While InStr(text, " ") > 0

            text = Mid(text, InStr(text, " "))

            text = Trim(text)

            txtinCell = txtinCell + 1

        Loop

        wordsinRange = wordsinRange + txtinCell

    End If

Next cell

MsgBox " Total No. of words found =" & wordsinRange

End Sub
Count Words in Spreadsheet in Excel VBA