Hi. Just note that it's not just Comments. For example...

Sub Demo()
Debug.Print [A1].SpecialCells(xlFormulas).Count
End Sub

Returns the count of the formulas on the sheet.
--
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in message
...
Very good Jim. It works (but I am still puzzled)
--
Gary's Student


"Jim Thomlinson" wrote:

Very interesting... This works (Tested)

Sub ccnt()
Dim rng As Range
Dim rngComments As Range
Dim i As Long

Set rng = Selection
On Error GoTo merr
Set rngComments = Intersect(rng, rng.SpecialCells(xlCellTypeComments))
MsgBox (rngComments.Cells.Count)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Thanks for your effort. Unfortunately your code performs exacltly like
mine.
--
Gary's Student


"Jim Thomlinson" wrote:

That is one of the pitfalls of using Selection is that XL will make a
best
guess for you. Similar to when you highlight a single cell and select
sort.
XL guesses that you are not trying to sort a single cell (there would
be no
point) so it selects the most appropraite area (best guess). Try
using a
range object set to the selection and you should be ok (untested)...

Sub ccnt()
dim rng as range

set rng = selection
i = 0
On Error GoTo merr
i = rng.SpecialCells(xlCellTypeComments).Count
MsgBox (i)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

I have been using:

Sub ccnt()
i = 0
On Error GoTo merr
i = Selection.SpecialCells(xlCellTypeComments).Count
MsgBox (i)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub


to count comments in a range. It accurately reports the number of
comments
for all multi-cell ranges.

If, however, I select only a single cell, it reports ALL the
comments on the
worksheet.

Why?
--
Gary's Student

Thanks to all of you.

Somehow I erroneously believed that range.SpecialCells() would always be a
proper subset of range. I was right most of the time, wrong in 256 * 65536
cases.
--
Gary's Student


"Tom Ogilvy" wrote:

Pretty Much All special cells functionality is meaningless on an single cell,
so as a convenience, a single cell as the anchor indicates the action should
be taken on the UsedRange. It really isn't more complex than that. This is
consistent both manually and with code.

--
Regards,
Tom Ogilvy


"Dana DeLouis" wrote:

Hi. Just note that it's not just Comments. For example...

Sub Demo()
Debug.Print [A1].SpecialCells(xlFormulas).Count
End Sub

Returns the count of the formulas on the sheet.
--
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in message
...
Very good Jim. It works (but I am still puzzled)
--
Gary's Student


"Jim Thomlinson" wrote:

Very interesting... This works (Tested)

Sub ccnt()
Dim rng As Range
Dim rngComments As Range
Dim i As Long

Set rng = Selection
On Error GoTo merr
Set rngComments = Intersect(rng, rng.SpecialCells(xlCellTypeComments))
MsgBox (rngComments.Cells.Count)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Thanks for your effort. Unfortunately your code performs exacltly like
mine.
--
Gary's Student


"Jim Thomlinson" wrote:

That is one of the pitfalls of using Selection is that XL will make a
best
guess for you. Similar to when you highlight a single cell and select
sort.
XL guesses that you are not trying to sort a single cell (there would
be no
point) so it selects the most appropraite area (best guess). Try
using a
range object set to the selection and you should be ok (untested)...

Sub ccnt()
dim rng as range

set rng = selection
i = 0
On Error GoTo merr
i = rng.SpecialCells(xlCellTypeComments).Count
MsgBox (i)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

I have been using:

Sub ccnt()
i = 0
On Error GoTo merr
i = Selection.SpecialCells(xlCellTypeComments).Count
MsgBox (i)
Exit Sub

merr:
MsgBox (i & " error")
Exit Sub
End Sub


to count comments in a range. It accurately reports the number of
comments
for all multi-cell ranges.

If, however, I select only a single cell, it reports ALL the
comments on the
worksheet.

Why?
--
Gary's Student