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

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" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

Not if there were two or more blanks.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <[email protected]> wrote in message
news:[email protected]...
> just curious, would counta wok in this instance or not?
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> just curious, would counta wok in this instance or not?
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >
> >
>
>

ok, got it, thanks

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> just curious, would counta wok in this instance or not?
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >
> >
>
>
>

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

--
Regards,
Tom Ogilvy

"Frank" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>