Excel- how to count all the cells that are not empty in a sheet? 论题张贴者: veratek
|
|---|
veratek
巴西
Local time: 10:40
French法语译成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 
法国
Local time: 15:40
正式会员 (自2011)
French法语译成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)
丹麦
Local time: 15:40
English英语译成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 | | 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
法国
Local time: 15:40
正式会员 (自2011)
French法语译成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? | Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
| | Anycount & Translation Office 3000 | Translation Office 3000
Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
