Excel delete all comments in Workbook with and without VBA

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA.

You may also want to read:

Excel consolidate all Comments in the workbook

Excel – delete all Comments in Worksheet

In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right click on any selected Cells > select Delete Comment.

If you are not certain where the Comments locate, instead of selecting specific Range, you can press ALT+A to select all Cells, then navigate to Review tab > Delete

One interesting findings is that if you select all Cells (e.g. pressing Ctrl+A), and then right click on any selected Cell, you will not find the Delete Comment option, therefore it is necessary to go to the Review tab although it is less convenient.

Alternatively, you can press F5 (Go to Special dialog) to select Cells that contain Comments instead of a Range, right click to delete them. (This method doesn’t work for multiple worksheets)

Excel – delete all comments in Workbook

Select multiple worksheets, and then press ALT+A to select all Cells.

Finally navigate to Review tab > Delete

Excel VBA – delete all comments in Workbook

If you want to write a Macro that runs several cleaning Procedures, VBA is a better choice.

Method 1 (Highest performance)

Press ALT+F11 and copy the below code in a new Module. When you run the Macro, all Comments in all worksheets will be deleted.

Private Sub rmComment()
    For Each ws In ActiveWorkbook.Sheets
        ws.Cells.ClearComments
    Next ws
End Sub

Method 2

Instead of using ClearComments Method on a Range, it is also possible to use Delete Method on Comment object.

Public Sub rmComment()
  Dim rng As Range
    For Each ws In ActiveWorkbook.Sheets
        For Each com In ws.Comments
            com.Delete
        Next com
    Next ws
  End Sub

Method 3

To check if a Comment exists in a Cell, delete Comment if it does. Although this has the lowest performance, I demonstrate this because there are circumstances where we need to check if a specific cell contains Comment.

Public Sub rmComment()
    For Each ws In ActiveWorkbook.Sheets
        For Each rng In ws.UsedRange
            If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
        Next rng
    Next ws
End Sub