Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to avoid counting blanks in a list
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 170
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 226
Default 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




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 226
Default 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








Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 170
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 170
Default 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





Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 170
Default 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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Avoid counting #N/A filled cells, or avoiding #N/A altogether fr8dog Excel Worksheet Functions 6 August 10th 08 12:03 AM
Excel 2002: How to avoid pasting blanks ? Mr. Low Excel Discussion (Misc queries) 3 September 15th 07 02:17 AM
Avoid plotting refferanced blanks as zeros in scatter plot graphs Jobe Charts and Charting in Excel 17 May 17th 07 03:15 PM
Counting Blanks in a list of dates by month phocused Excel Worksheet Functions 3 January 2nd 07 02:12 PM
counting blanks in a list capgunkidd[_3_] Excel Programming 3 May 12th 04 03:52 PM


All times are GMT +1. The time now is 04:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement