Excel VBA: Deleting Comments from an Active Sheet

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

Macro Example

Sub DeleteComments_ActiveSheet()

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeComments).ClearComments

End Sub

Description

On Error Resume Next

Sub...

  On Error Resume Next

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

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

Deleting Comments

Sub...

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeComments).ClearComments

The 5th line deletes comments from an active worksheet.

[Application.]Cells

Cells...

The Application.Cells property returns the Range object represents ALL ranges on an active sheet.
So we do not need to loop through.

obj-Excel-Application-Cells

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

obj-Excel-Cells

Cells.SpecialCells [Range.SpecialCells]

Cells.SpecialCells...

The Cells.SpecialCells (Range.SpecialCells method) returns a Range object that represents all the cells that match the specified type and value.

obj-Excel-Range-SpecialCells

Cells.SpecialCells(xlCellTypeComments)...

The Range.SpecialCells method requires the Type parameter that represents the type of special cells. In this case, I use xlCellTypeComments.

obj-Excel-XlCellType-xlCellTypeComments

It refers to cells that contains a comment.

SpecialCells(xlCellTypeComments).ClearComments [Range.ClearComments]

Cells.SpecialCells(xlCellTypeConstants).ClearComments

The Range.ClearComments method clears all cell comments from the specified range.

obj-Excel-Range-ClearComments

Ends the Macro

After the 5th line evaluated, this macro ends.

Sub DeleteComments_ActiveSheet()

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeComments).ClearComments

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007