How to avoid counting blanks in a list
 

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

How to avoid counting blanks in a list
 

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

---
Regards,
Norman


"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

How to avoid counting blanks in a list
 

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

How to avoid counting blanks in a list
 

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

How to avoid counting blanks in a list
 

Not if there were two or more blanks.

--
Regards,
Tom Ogilvy


"Gary Keramidas" wrote in message
...
just curious, would counta wok in this instance or not?

--


Gary


"Tom Ogilvy" wrote in message
...
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

How to avoid counting blanks in a list
 

Sorry, I was thinking you were asking something else.

No, CountA would not differentiate between duplicate entries.

the OP stated: unique numbers

--
Regards,
Tom Ogilvy


"Gary Keramidas" wrote in message
...
just curious, would counta wok in this instance or not?

--


Gary


"Tom Ogilvy" wrote in message
...
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

How to avoid counting blanks in a list
 

ok, got it, thanks

--


Gary


"Tom Ogilvy" wrote in message
...
Sorry, I was thinking you were asking something else.

No, CountA would not differentiate between duplicate entries.

the OP stated: unique numbers

--
Regards,
Tom Ogilvy


"Gary Keramidas" wrote in message
...
just curious, would counta wok in this instance or not?

--


Gary


"Tom Ogilvy" wrote in message
...
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

How to avoid counting blanks in a list
 

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

How to avoid counting blanks in a list
 

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

"Gary Keramidas" wrote:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

How to avoid counting blanks in a list
 

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

How to avoid counting blanks in a list
 

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