Counting non-blank cells in a column

Ron · Guest

Does anyone have a formula for counting the number of cells in a column that
contain text?

We are trying to count the number of names that appear in a column.

Google · Sponsor

Lene Fredborg · Guest

You can use a macro. The following macro will count all non-blank cells in
the (first) column in the selection. A message displays the result.

You do not need to select the entire column before running the macro. It
will check all cells even if you have only an insertion point or if part of
the column selected.

Note that the macro will consider a cell as non-blank even if the cell
contains spaces only. Additional code can be added to prevent this if desired.

Sub CountNonBlankCellsInColumn()

Dim oCell As Cell
Dim n As Long 'used as counter

n = 0
'Check whether selection is in table
If Selection.Information(wdWithInTable) = False Then
MsgBox "The selection must be in a table. Please retry.", vbOKOnly
Exit Sub
End If
‘Check all cells in the first column in the selection
For Each oCell In Selection.Columns(1).Cells
'Check whether row is empty - delete if it is
If Len(oCell.Range.Text) > 2 Then
'Cell is not empty - count
n = n + 1
End If
Next oCell

'Show message
MsgBox "The (first) column in the selection contains " & n & _
" non-blank cells."
End Sub

For help on installing macros, see:
http://www.gmayor.com/installing_macro.htm

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

"Ron" wrote: