Count Cells with Text Only
 

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.

Count Cells with Text Only
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
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.

Count Cells with Text Only
 

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



In article .com,
wrote:

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.

Count Cells with Text Only
 

You may want to use
ElseIf Len(Trim(mycell)) = 0 Then
to avoid counting a single quote followed by spaces
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
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.