Count Cells with Text Only

D

dswiders

Does anyone know of a VBA code that I can use that will count the
number of cells in a selected column that contain text only, no blanks
or formulas? Thank you.
 
B

Bernard Liengme

Try this
Function TextCount(myrange)
For Each mycell In myrange
If mycell.HasFormula Then
'do nothing
ElseIf IsNumeric(mycell) Then
'do nothing
ElseIf Len(mycell) = 0 Then
'do nothing
Else
TextCount = TextCount + 1
End If
Next
End Function

called with, for example, =TEXTCOUNT(A5:A24)
Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
 
J

JE McGimpsey

One way:

Dim nCount As Long
On Error Resume Next
nCount = Selection.SpecialCells( _
Type:=xlCellTypeConstants, _
Value:=xlTextValues).Count
On Error GoTo 0
MsgBox "Number of Text Values: " & nCount
 
B

Bernard Liengme

You may want to use
ElseIf Len(Trim(mycell)) = 0 Then
to avoid counting a single quote followed by spaces
 
Top