Excel consolidate all Comments in the workbook

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro.

You may also want to read:

Excel show all comments in worksheet

Excel delete all comments in Workbook

Excel consolidate all Comments in the workbook

Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook.

VBA Code – consolidate all comments

Public Sub consolComments()
counter = 1
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("show comments").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Set newWS = ActiveWorkbook.Worksheets.Add
    
    newWS.Name = "show comments"
    newWS.Range("A1").Value = "Worksheet"
    newWS.Range("B1").Value = "Address"
    newWS.Range("C1").Value = "Value"
    newWS.Range("D1").Value = "Comment"

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "show comments" Then
            Set allcommentrng = Nothing
            On Error Resume Next
            Set allcommentrng = ws.Cells.SpecialCells(xlCellTypeComments)
            On Error GoTo 0

            If Not allcommentrng Is Nothing Then
                For Each Rng In allcommentrng
                    counter = counter + 1
                    newWS.Range("A" & counter).Value = ws.Name
                    newWS.Range("B" & counter).Value = Rng.Address
                    newWS.Range("C" & counter).Value = Rng.Value
                    newWS.Range("D" & counter).Value = Rng.Comment.Text
                Next Rng
            End If
        End If
    Next ws
    
    If Sheets("show comments").Range("A2").Value = "" Then
        Application.DisplayAlerts = False
        Sheets("show comments").Delete
        Application.DisplayAlerts = True
        MsgBox ("No comments in this workbook")
    End If
End Sub
Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0
End Function

Explanation of VBA Code

– delete worksheet “show comments”, if any

– create worksheet “show comments” with headers

– for each worksheet, name all the Comments Cell as allcommentrng

– if there are Comments, write down in “show comments”

– delete “show comments” if it contains no data

Demonstration – consolidate all comments

In the below example, there are two worksheets that contain Comments. Our goal is to consolidate the Comments in both worksheets in a new worksheet.

excel_consolidate_all_comments

Run the Macro, then a worksheet “show comments” is created in the end. The “Value” field is the value in the Comment Cell.

excel_consolidate_all_comments_02

Outbound References

http://www.excelforum.com/excel-formulas-and-functions/493625-macro-delete-comments-what-if-theres-none.html

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *