Excel- how to count all the cells that are not empty in a sheet? Thread poster: veratek
|
|---|
veratek
Brazil
Local time: 07:12
French to English
+ ...
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 ad... See more 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. ▲ Collapse
| | | | wotswot 
France
Local time: 12:12
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... See more 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 ▲ Collapse
| | | |
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... See more 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." ▲ Collapse
| | | | Annelise Brincker (X)
Denmark
Local time: 12:12
English to Danish
+ ...
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: 12:12
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
| | | | wotswot
France
Local time: 12:12
Member (2011)
French to English
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? | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
| | CafeTran Espresso | You've never met a CAT tool this clever!
Translate faster & easier, using a sophisticated CAT tool built by a translator / developer.
Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools.
Download and start using CafeTran Espresso -- for free
Buy now! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
