SpecialCells problem
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
|