Excel- how to count all the cells that are not empty in a sheet?
Thread poster: veratek

veratek
Brazil
Local time: 00:56
French to English
+ ...
Jun 24, 2012

Hi, any Excel gurus out there?

I have a worksheet and I need to know how many cells contain at least some text, that is, the number of cells that are not empty. I don't need to know anything about the text in the cells.

I have a file with three sheets. So either something that I can apply to each sheet and I'll just add the three totals, or a big total for the whole file (with the three sheets).

Anyone know how to do this?

Thanks so much in advance.


Direct link Reply with quote
 
wotswot  Identity Verified
France
Local time: 03:56
Member (2011)
French to English
Count non-empty cells Jun 25, 2012

Here you go:

------------------------------------
Sub CountNonEmptyCells()
Dim WS As Worksheet
Dim NBRows, NBCols, rc, cc As Long
Dim WSTot, WBTot As Long
Dim Msg As String
For Each WS In ThisWorkbook.Worksheets
WSTot = 0
NBRows = WS.UsedRange.Rows.Count
NBCols = WS.UsedRange.Columns.Count
For rc = 1 To NBRows
For cc = 1 To NBCols
If Not IsEmpty(WS.Cells(rc, cc).Value) Then
WSTot = WSTot + 1
WBTot = WBTot + 1
End If
Next cc
Next rc
Msg = Msg & "Worksheet " & WS.Name & " contains " & WSTot & " non-empty cells" & vbNewLine
Next WS
Msg = Msg & String(30, "=") & vbNewLine
Msg = Msg & "Workbook " & ThisWorkbook.Name & " contains " & WBTot & " non-empty cells" & vbNewLine
MsgBox Msg
End Sub
----------------------------------

Regards


Direct link Reply with quote
 

Javier Wasserzug  Identity Verified
United States
Local time: 18:56
English to Spanish
+ ...
COUNTA Jun 25, 2012

The COUNTA function will count cells that are not empty. Its syntax is:
=COUNTA(value1, value2,...value30).

The arguments (e.g. value1) can be cell references, or values typed into the formula.

The following example uses one argument -- a reference to cells A1:A5.

Enter the sample data on your worksheet
In cell A7, enter a COUNTA formula, to count the numbers in column A: =COUNTA(A1:A5)

Press the Enter key, to complete the formula.

The result will be 4, the number of cells that contain data.

Note: COUNTA will count cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). It will also count cells which had formulas that evaluated to "", but then were converted to values (Edit | Paste Special, Values). You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Tools | Options, Transition tab, "Transition navigation keys."


Direct link Reply with quote
 
Annelise Brincker  Identity Verified
Denmark
Local time: 03:56
English to Danish
+ ...
COUNTA Jun 25, 2012

If you want to count it fx in column L, write the following in a cell =COUNTA(l:l)

Direct link Reply with quote
 
Post removed: This post was hidden by a moderator or staff member for the following reason: user asked for it

Rolf Keller
Germany
Local time: 03:56
English to German
Corrected macro Jun 25, 2012

wotswot wrote:

Here you go:

------------------------------------
Sub CountNonEmptyCells()
...


The macro displays wrong values if the used range starts at a row/colum# > 1.
I corrected it for you:

------------------------------------
Sub CountNonEmptyCells()
Dim WS As Worksheet
Dim WSTot, WBTot As Long
Dim Msg As String
For Each WS In ActiveWorkbook.Worksheets

WSTot = 0

For Each CELL In WS.UsedRange.Cells
If Not IsEmpty(CELL.Value) Then
WSTot = WSTot + 1
WBTot = WBTot + 1
End If

Next CELL

Msg = Msg & "Worksheet " & WS.NAME & " contains " & WSTot & " non-empty cells" & vbNewLine
Next WS

Msg = Msg & String(30, "=") & vbNewLine
Msg = Msg & "Workbook " & ThisWorkbook.NAME & " contains " & WBTot & " non-empty cells" & vbNewLine
MsgBox Msg
End Sub


Direct link Reply with quote
 
wotswot  Identity Verified
France
Local time: 03:56
Member (2011)
French to English
@Rolf Jun 25, 2012

Thanks for that Rolf, a useful tweak.
You don't need the "WSTot = 0" though; it iz zero by default as soon as it is declared (Dim) !


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Excel- how to count all the cells that are not empty in a sheet?

Advanced search






Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
SDL Trados Studio 2017 Freelance
The leading translation software used by over 250,000 translators.

SDL Trados Studio 2017 helps translators increase translation productivity whilst ensuring quality. Combining translation memory, terminology management and machine translation in one simple and easy-to-use environment.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search