Question

如何用vba对统计一列有多少个有数值的单元格

现在需要做一个小程序，很简单，就是要统计一列有多少个有数值的单元格，请问如何编写？ 就是按一下某个按钮就能在某个单元格中显示结果 谢谢

Answer 1

直接调用工作表函数来统计
Private Sub CommandButton1_Click()
a = Application.WorksheetFunction.CountA(Range("a1:a60000"))
MsgBox a
End Sub
如果统计其他的可以把countA换成你需要的

Answer 2

http://wenku.baidu.com/view/d7d6ea5c3b3567ec102d8a99.html

可点上面的链接下载该程序的源代码

Sub 选区字符统计()
Dim str As String, ChineseChar As Long, Alphabetic As Long, Number As Long
Dim i As Long, rng As Range, j As Long
For Each rng In Selection
j = j + Len(rng.Value) '计算字符总长度
For i = 1 To Len(rng)
str = Mid(rng.Value, i, 1)
If str Like "[一-龥]" = True Then '汉字
ChineseChar = ChineseChar + 1 '汉字累加
ElseIf str Like "[a-zA-Z]" = True Then '字母
Alphabetic = Alphabetic + 1 '字母累加
ElseIf str Like "[0-9]" = True Then '数字
Number = Number + 1 '数字累加
End If
Next
Next
MsgBox "所选单元格区域中共有字数" & j & "个。" & vbCrLf & "汉字：" & ChineseChar & "个" & _
vbCrLf & "字母：" & Alphabetic & "个" & vbCrLf & "数字：" & Number & "个" & vbCrLf _
& "特殊字符及空格:" & j - Alphabetic - Number - ChineseChar & "个。", vbInformation, "字符统计"
End Sub

简单点可用以下代码
Sub 宏2()
On Error Resume Next
MsgBox Selection.SpecialCells(xlCellTypeConstants, 1).Count
End Sub

Answer 3

'假设对第3行操纵
dim i as integer
dim MySum as integer
Mysum=0
for i=1 to 300
if cells(3,i)<>"" then MySum=MySum +1
next i

Answer 4

这个不需要VBA，用COUNT函数即可。