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 Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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 *