Excel, just like other Office products, supports comments. Although comments are helpful to add tips or other details into your spreadsheets, deleting them may become time consuming while trying to find and select all of them. In this article, we're going to show you how to delete all comments in Excel using VBA.

How to delete all comments in Excel

Comments are stored in the comments collection under a worksheet object. This means that we can loop through comments in a worksheet and delete them one by one (automatically of course!).

    For Each cmt In ActiveSheet.Comments
        cmt.Delete
    Next cmt

To delete the Comments in whole workbook, we need to check every sheet as well.

    For Each ws In ActiveWorkbook.Worksheets
       For Each cmt In ws.Comments
            cmt.Delete
        Next cmt
    Next ws

You can use codes in two ways:

• Module
• Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

Delete Comments in an active sheet

Module method:

Sub DeleteCommentsInActiveWorksheet()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Delete
    Next cmt
End Sub

Immediate Window method:

For Each cmt In ActiveSheet.Comments: cmt.Delete: Next cmt

Delete Comments in a workbook

Module method:

Sub DeleteCommentsInActiveWorkbook()
    Dim ws As Worksheet
    Dim cmt As Comment
    For Each ws In ActiveWorkbook.Worksheets
        For Each cmt In ws.Comments
            cmt.Delete
        Next cmt
    Next ws
End Sub

Immediate Window method:

For Each ws In ActiveWorkbook.Worksheets: For Each cmt In ws.Comments: cmt.Delete: Next cmt: Next ws