Bob,
Thanks for your speedyreply. If I am correct, neither count nor counta can
deal with duplicated data, I need to count "unique values". I tried
array-frequency, its works but slow in dealing with 65536 rows, thats why I
guess the VBA could speed up in getting this counting result.
Thanks again.

"Bob Umlas, Excel MVP" wrote:

You don't need vba to do it -- to count numbers:
=COUNT(Sheet1!A2:A65536)
to count text which does not include numbers:
=COUNTA(Sheet1!A2:A65536)-COUNT(Sheet1!A2:A65536)

HTH

"Seeker" wrote:

I need to count numerical & text from A2:A65536 in €œsheet B€� and would like
to have the counting result shows in €œsheet A€� cell A1. I found a VBA posted
on 12 Jan 2007 which may fit my needs. However I am illiteracy to VBA and
dont know how to embed following VBA to my current one which was done by
macro recorder. I also need help in replacing information to fit the range
name & sheet name by using this VBA.
Thanks.

Function CountValues(TheRange As Range) As Integer
Dim SaveValues(99) As Variant
Dim Index As Integer
Dim MaxIndex As Integer
Dim FoundValue As Boolean
Index = 0
MaxIndex = 0
Dim cell As Range
For Each cell In TheRange
If IsNumeric(cell) Then
TheValue = Val(cell.Value)
If MaxIndex = 0 Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
Else
FoundValue = False
Index = 0
While Not FoundValue And Index <= MaxIndex
FoundValue = (SaveValues(Index) = TheValue)
If Not FoundValue Then Index = Index + 1
Wend
If Not FoundValue Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
End If
End If
End If
Next
CountValues = MaxIndex
End Function