Excel Extract Cell Comments

Sub CreateCommentsSummary()

Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer

' get all cells with comment

Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

' get cell reference where user want to place the summary

Set rgOutput = _

Application.InputBox(Prompt:="Select cell where you want to put the comments summary", _

Title:="Comments Summary", Type:=8)

iRow = rgOutput.Row

iCol = rgOutput.Column

' read each cell with comment and build the summary

For Each rgCell In rgComments

Cells(iRow, iCol) = rgCell.Address    ' print cell address

Cells(iRow, iCol + 1) = rgCell.Value    ' print cell value

Cells(iRow, iCol + 2) = rgCell.Comment.Text    'print cell comment text

iRow = iRow + 1

Next rgCell

End Sub