How to avoid counting blanks in a list

[Guest]

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

 

[Norman Jones]

Hi Frank,

Change:

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

to:

For Each rCel In Selection
If Not IsEmpty(rCel.Value) Then
clFilter.Add Str(rCel.Value), Str(rCel.Value)
End If
Next rCel

 

[Tom Ogilvy]

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

 

[Gary Keramidas]

just curious, would counta wok in this instance or not?

 

[Tom Ogilvy]

Not if there were two or more blanks.

 

[Tom Ogilvy]

Sorry, I was thinking you were asking something else.

No, CountA would not differentiate between duplicate entries.

the OP stated: >unique numbers

 

[Gary Keramidas]

ok, got it, thanks

--


Gary

Sorry, I was thinking you were asking something else.

No, CountA would not differentiate between duplicate entries.

the OP stated: >unique numbers

 

[Guest]

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

 

[Guest]

I have tried your suggestion, but it doesn't seems to work. The sMsg string
only includes the first number in the column followed by a comma and a zero.

I don't understand why it won't work.

Regards

Frank

 

[Tom Ogilvy]

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

 

[Guest]

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