Hi Don,
Tks your kindness in providing the solution to me. However, the SUMPRODUCT
takes more than 30 seconds to perform and the VBA is too difficult for me to
understand and replace respetive information within your coding.
After hours in exploring excel help, I found =large() can provide the
largest figures
even though I have duplicate figures within range, and it only takes a
second to get the result. Since this function only works on numeric data, now
I meet another problem.
The value is from an output of formula
=LEFT(C67,LEN(C67)-(LEN(C67)-FIND(".",C67)+1)) , after copy - paste special -
value, value has a warning message of "
The value of this cell is formatted as text or preceded by an apostrophe",
it needs a manul action by pressing the icon and select the "Convert to
number". I tried macro recorder to record action of "Convert to number", but
recorder doesn't record anything, I also tried to format it as number by
using --Selection.NumberFormatLocal = "0", it also doesn't work. Any idea I
can auto-change it back to numberic data please?
"Don Guillett" wrote:
How about a regular function.
=SUMPRODUCT((A1:A15<"")/COUNTIF(A1:A15,A1:A15&""))
or vba
Sub countunique()
Dim c As Range, UC As New Collection
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
UC.Add c.Value, CStr(c.Value)
Next c
'MsgBox UC.Count
Range("b8") = UC.Count
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Seeker" wrote in message
...
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