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

Related Entries

• Getting Cell Reference With Input Box

  We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel. Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.Sub CopyPaste()Dim InputCells As Excel.Range Dim...

• How To Find the Last Row That Contain Data in Excel?

  UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

• How To Find Row Position of a Particular Text

  Sometimes in Excel, we need to find the row position of a particular text, maybe to be able to paste certain of data right below the corresponding text or else. Very useful if we have an Excel Worksheet Template that we use a lot, with fix header on it.' Input param: Text we want to look...

• Remove Non-AlphaNumeric Characters from String

  The function below will allow us to remove a non alphanumeric character from an input string. Be careful though, lengther the string, more time needed to evaluate each string on the sentence. To bad that I don’t have any idea on how to use Regular Expression in Excel VBA, if we can use regex to replace...

• Handy VBA To Calculate Person Age

  This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.Sub PersonAge() Dim iYears As Integer, iMonths...

Valuable Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

• Top Tips & Tricks to Get You Started in Microsoft Excel 2007

  This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.

• Microsoft Office 2003/XP/2007 Training Videos

  Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.