Count all the comments in a active excel sheets

Hi all,

I am back again with some new excel tricks.

Today we are talking about how to count all the comments in a active excel sheet.

For doing this we use the macro. Macro is a more power full tools of excel.

For open the visual basic editor in excel do the following.

tools>macro>visual basic editor or press alt+F11

Now insert a new module from insert menu. Now copy and paste the following code under it

Sub CountComments()
commentcount = 0

For Each cell In ActiveSheet.UsedRange

On Error Resume Next

x = cell.Comment.Text

If Err = 0 Then commentcount = commentcount + 1

Next cell

MsgBox "Total Comments " & commentcount, vbOKOnly, "Sudipto Counts it"

End Sub

Now save your workbook.

from the tools menu select macro and the choose CountComments and click on options button from the window. And choose a short cut key for it, in this case i am using ctrl + m.

Insert few comments on the worksheet you want and press ctrl+m and you can see how much comments in the current active worksheet.

If you want to use the function for all of your workbook you can save your macro workbook as a addin.

For any clarification you can send me a mail.

Thank you.


Download the addins for countcomments: Click Here