Tom's code worked fine for me.

But if you want to declare the variables, make it look something like:

Dim nodupes As New Collection
Dim rng As Range
Dim cell As Range
Dim i As Long
Dim itm As Variant



Papa Jonah wrote:

Tom,
I think I understand the concept now. However, I am experimenting with your
code and vba does not seem to like your "For each cell in rng" statement.
I have created a statement:
dim rng as range

Should I have not created the dim? or does the For statement need to be
tweaked?

Thanks

"Tom Ogilvy" wrote:

The D means write the results in Column D.


You can loop through the cells and add the items to a collection, setting
the index property. This will prevent duplicates. Then use that list to
write the data.


Sub IJKL()
Dim nodupes As New Collection

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, Key:=cell.Text
Next
On Error GoTo 0
i = 0
For Each itm In nodupes
Debug.Print itm
i = i + 1
Cells(i, "D") = itm
Cells(i, "E").Formula = "=Countif(" & _
rng.Address & "," & Cells(i, "D").Address & _
")"
' or
' Cells(i, "E").Value = Application.CountIf( _
' rng, itm)
Next
End Sub

If you want the list sorted, throw in a sort as the last statement.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
Tom,
I am also interested in doing something like this. However, I do not
understand your solution. What is the "D" for?
Also, is there a way to do this such that the code determines which
entries
are present and only counts those. For example, if in Alan's example, the
number 3 was never entered, the code would only count the number of times
1,
2, 4, 5, and 6 were entered in the range?

TIA

"Tom Ogilvy" wrote:

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of
any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in
the
column and return the counts to a set of cells
can you help please










--

Dave Peterson