This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA.
You may also want to read:
Excel show all comments in worksheet
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