|
| |
• Up • AutoFilter • Count words • Sumproduct • Pivot Tables •
Excel - Count words in Excel
As there is no wordcount in excel, I have adapted a word count macro so that it
will encompass an entire workbook.
It will count all words in the specified range, on each sheet. The macro first
looks at all sheets, and finds the maximum range.
It then counts the words.
Copy the code into a new module, or link it to a control button.
Sub CountWords()
Dim WS_Count As Integer
Dim I As Integer
Dim MyRange As Range
Dim CellCount As Long
Dim TotalWords As Long
Dim NumWords As Integer, ColCount As Integer, ColCount1 As Integer, Rowcount As
Integer, Rowcount1 As Integer
Dim Raw As String
TotalWords = 0
ColCount = 0
ColCount1 = 0
Rowcount = 0
Rowcount1 = 0
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
On Error Resume Next 'Will continue if an error results
ColCount1 = ActiveWorkbook.Worksheets(I).UsedRange.Columns.Count
If ColCount1 > ColCount Then ColCount = ColCount1
Rowcount1 = ActiveWorkbook.Worksheets(I).UsedRange.Rows.Count
If Rowcount1 > Rowcount Then Rowcount = Rowcount1
Next I
Lcell = Range("A1").Cells(Rowcount, ColCount).Address
For I = 1 To WS_Count
Set MyRange = ActiveWorkbook.Worksheets(I).Range("A1", Lcell)
For CellCount = 1 To MyRange.Cells.Count
If Not MyRange.Cells(CellCount).HasFormula Then
Raw = Trim(MyRange.Cells(CellCount).Value)
If Len(Raw) > 0 Then
NumWords = 1
Else
NumWords = 0
End If
While InStr(Raw, " ") > 0
Raw = Mid(Raw, InStr(Raw, " "))
Raw = Trim(Raw)
NumWords = NumWords + 1
Wend
TotalWords = TotalWords + NumWords
End If
Next CellCount
Next I
MsgBox "There are " & TotalWords & " words in the selection."
End Sub
| |
|
