Welome Excellers, it is time for another #MacroMonday #Excel blog post in my 2019 series.  Do you have an Excel workbook which has a lot of comments that you do not need anymore?.  Are there too many comments to  find and clear individually?. Well, this is a great Excel macro which will clear all comments from an Excel workbook for you.

Comments in an Excel worksheet are stored in the comments collection under the Worksheet object.    Therefore we can loop through all of the comments and delete then using some simple code.

We need to start off the macro by inserting a New Module.  So, this is done by selecting the Personal.xlsb workbook, then Insert Module. 

Starting To Write The Macro.

Ok, we need to type Sub then the name of your macro.  In this example, I have called it simply ClearComments.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub ClearComments
End Sub
[/stextbox]

Declaring Variables.

The first piece of code we need to write is a DIM statement. We need to declare a variable. In this case, it is cmt As Comment. This simply creates a memory container in Excel for these values.

[stextbox id=’info’]
Dim cmt As Comment
[/stextbox]

Loop Through All Comments And Delete.

For the next part of this Excel macro we can use the For Each looping method. We simply loop through all of the comments in the active worksheet and delete them. The code restricts the comments being deleted to the currently selected or active worksheet.

[stextbox id=’info’]
For Each cmt In ActiveSheet.Comments
cmt.Delete
Next cmt
[/stextbox]

If you need to clear all of the comments from your total Excel workbook then the code just needs to be tweaked a small bit.

Ending The Macro.

Finally, once all of the comments in the active worksheet have been deleted then the code comes to an end. This is done with the End Sub piece of code, this was already inserted into the code when we started writing the macro.

[stextbox id=’info’]
End Sub
[/stextbox]

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series then click on the link below

Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  Need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.