Just a correction to do with the posted link.
dhSortedDictionary should not be used (it can cause an error) and instead
one should use the file
dhRichClient, which can be downloaded from:
www.datenhaus.de/Downloads/dhRichClientDemo.zip
This has an updated version of cSortedDictionary and beside that a lot more,
eg a very good
VB(A) wrapper for SQLite.

RBS


"RB Smissaert" wrote in message
...
This VBA code will run quite fast:

Function CountUnique(rng As Range) As Long

Dim i As Long
Dim arr
Dim coll As Collection

arr = rng

Set coll = New Collection

On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i

CountUnique = coll.Count

End Function


Sub test()

MsgBox CountUnique(Selection)

End Sub

So, in your case select the range B2:B65536 and run the Sub test.
If still not fast enough than use Olaf Schmidt's dhSortedDictionary, which
can be downloaded from he
http://www.thecommon.net/9.html


RBS



"SiH23" wrote in message
...
I would be most grateful if someone could offer me some help.

I need to count the number of unique values within a column range. The
range
is between B2:B65536. Each value consists of a series of alpha and
numerical
characters and there are blank cells within the range.

There will be around 30,000 plus entries.

I have used various formulas, but due to the vast number of rows of data
they crash.

An example of one of the formulas I used is below:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

I am desperate for an answer but am confused as to how to do this? Any
help
or assistance would be very much greatly appreciated.