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

veratek
Brazil
Local time: 21:35
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.


 

wotswot  Identity Verified
France
Local time: 02:35
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


 

Javier Wasserzug  Identity Verified
United States
Local time: 17:35
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."


 

Annelise Brincker  Identity Verified
Denmark
Local time: 02:35
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)

 
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: 02:35
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: icon_wink.gif

------------------------------------
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


 

wotswot  Identity Verified
France
Local time: 02:35
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) !


 


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






memoQ translator pro
Kilgray's memoQ is the world's fastest developing integrated localization & translation environment rendering you more productive and efficient.

With our advanced file filters, unlimited language and advanced file support, memoQ translator pro has been designed for translators and reviewers who work on their own, with other translators or in team-based translation projects.

More info »
BaccS – Business Accounting Software
Modern desktop project management for freelance translators

BaccS makes it easy for translators to manage their projects, schedule tasks, create invoices, and view highly customizable reports. User-friendly, ProZ.com integration, community-driven development – a few reasons BaccS is trusted by translators!

More info »



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