With my modification, it
Works fine for me with numbers.

--
Regards,
Tom Ogilvy

"Frank" wrote in message
...
I don't know what is wrong, but the sMsg string seems to only include the
first number in the list, following a comma and a zero.
Any suggestion on why it doesn't work?

Regards

Frank


"Tom Ogilvy" wrote:

For Each rCel In Selection
if len(trim(rCel)) 0 then
clFilter.Add Str(rCel.Value), Str(rCel.Value)
end if
Next rCel


--
Regards,
Tom Ogilvy

"Frank" wrote in message
...
I have received this VBA code to that search a column for unique
numbers.
These numbers are then stored in a string:

Dim rCel As Range, clFilter As Collection
Dim iCntr As Integer
Dim sMsg As String

Worksheets("Sheet1").Range("A9:A65536").Select
Set clFilter = New Collection

On Error Resume Next
For Each rCel In Selection
clFilter.Add Str(rCel.Value), Str(rCel.Value)
Next rCel

For iCntr = 0 To clFilter.Count - 1
If iCntr < clFilter.Count - 1 Then sMsg = sMsg &
clFilter(iCntr) &
", "
If iCntr = clFilter.Count - 1 Then sMsg = sMsg &
clFilter(iCntr)

Next iCntr

However, this code also counts blanks.
How do I avoid adding the occurences of blanks (represented by a zero)
in
the sMsg string?


Thanks

Frank

Don't know why, but I had to modify the code like this in order to make it
work [i.e using clFilter(iCntr) instead of clFilter(iCntr) - 1]

Regards,
Frank Krogh


On Error Resume Next
For Each rCel In Selection
If Len(Trim(rCel)) 0 Then clFilter.Add Str(rCel.Value),
Str(rCel.Value)
Next rCel

For iCntr = -1 To clFilter.Count
If iCntr < clFilter.Count Then sMsg = sMsg & clFilter(iCntr) & ", "
If iCntr = clFilter.Count Then sMsg = sMsg & clFilter(iCntr)
Next iCntr


"Tom Ogilvy" wrote:

With my modification, it
Works fine for me with numbers.

--
Regards,
Tom Ogilvy

"Frank" wrote in message
...
I don't know what is wrong, but the sMsg string seems to only include the
first number in the list, following a comma and a zero.
Any suggestion on why it doesn't work?

Regards

Frank


"Tom Ogilvy" wrote:

For Each rCel In Selection
if len(trim(rCel)) 0 then
clFilter.Add Str(rCel.Value), Str(rCel.Value)
end if
Next rCel


--
Regards,
Tom Ogilvy

"Frank" wrote in message
...
I have received this VBA code to that search a column for unique
numbers.
These numbers are then stored in a string:

Dim rCel As Range, clFilter As Collection
Dim iCntr As Integer
Dim sMsg As String

Worksheets("Sheet1").Range("A9:A65536").Select
Set clFilter = New Collection

On Error Resume Next
For Each rCel In Selection
clFilter.Add Str(rCel.Value), Str(rCel.Value)
Next rCel

For iCntr = 0 To clFilter.Count - 1
If iCntr < clFilter.Count - 1 Then sMsg = sMsg &
clFilter(iCntr) &
", "
If iCntr = clFilter.Count - 1 Then sMsg = sMsg &
clFilter(iCntr)

Next iCntr

However, this code also counts blanks.
How do I avoid adding the occurences of blanks (represented by a zero)
in
the sMsg string?


Thanks

Frank