Excel Macro: Deleting Comments from an Active Workbook

This Excel VBA macro removes or deletes all comments from an active workbook.

Macro Example

Sub DeleteComments_ActiveWorkbook()

  Dim sh As Worksheet

  On Error Resume Next

  For Each sh In Worksheets
    sh.Cells.SpecialCells(xlCellTypeComments).ClearComments
  Next

End Sub

Description

Variable Declaration

The 3rd line declares an object variable called sh which will refer to a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

On Error Resume Next

Sub...

  Dim sh As Worksheet

  On Error Resume Next

A run-time error will occur at the 8th line, if there is not any cells that has a comment.

The 5rd line aims to ignore this run-time error.

Starts Looping

Sub...

  Dim sh As Worksheet

  On Error Resume Next

  For Each sh In Worksheets

The 7th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

[Application.]Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Deleting Comments

Sub...

  Dim sh As Worksheet

  On Error Resume Next

  For Each sh In Worksheets
    sh.Cells.SpecialCells(xlCellTypeComments).ClearComments

The 8th line deletes comments from a worksheet.

If you would like to understand this, please read this article.

Loops Back

Sub...

  Dim sh As Worksheet

  On Error Resume Next

  For Each sh In Worksheets
    sh.Cells.SpecialCells(xlCellTypeComments).ClearComments
  Next sh

The 9th line loops back to get the next worksheet.

After every comment is deleted, this macro ends.

Sub DeleteComments_ActiveWorkbook()

  Dim sh As Worksheet

  On Error Resume Next

  For Each sh In Worksheets
    sh.Cells.SpecialCells(xlCellTypeComments).ClearComments
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/018237.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007