Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Tables / December 2007

Tip: Looking for answers? Try searching our database.

Counting non-blank cells in a column

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ron - 18 Dec 2007 20:51 GMT
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.
Lene Fredborg - 18 Dec 2007 23:41 GMT
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

Signature

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

> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.