Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
Count Cells That Don't Contain the Following Text Strings | Excel Discussion (Misc queries) | |||
How do I count cells with text but ignore cells with spaces? | Excel Discussion (Misc queries) | |||
How do I count cells with text in them? | Excel Discussion (Misc queries) | |||
count cells containing text | Excel Worksheet Functions |