Comment in excel is a way of annotate calculation logic of certain measure or KPI. It will be very useful to transfer data manipulation logic user to user. As a results, there is extension use of comments take place in a workbook. It will be very difficult to check each and every comments if comments scattered in different ranges in the workbook. Thus, it will be a quite problematic task to gather information of all the comments on the singe place.
In this situation a simple excel macro will be very helpful to gather all comments in a list. Using excel macro you can list all the comments within a minute.
Here is the code which will be very useful to list down all the comments from the workbook:
Sub List_All_Comments() ' Author: Dreams24 ' Written for VBA Tricks and tips blog ' https://www.vbatricksntips.com 'Declare Variable Dim nSht As Worksheet Dim Sht As Worksheet Dim Rng As Range Dim i As Integer Dim cell As Variant Application.ScreenUpdating = False Application.DisplayAlerts = False 'Add new sheet in the workbook with "Comment List" name Set nSht = Sheets.Add nSht.Name = "Comment List" nSht.Range("A1") = "Sheet Name" nSht.Range("B1") = "Cell Address" nSht.Range("C1") = "Comments" nSht.Range("D1") = "Cell value" i = 2 'Loop through all sheets in a workbook For Each Sht In ActiveWorkbook.Worksheets On Error Resume Next 'Set Rng for comment ranges Set Rng = Sht.Cells.SpecialCells(xlCellTypeComments) If Not Rng Is Nothing Then 'Loop through all comment ranges For Each cell In Rng nSht.Range("A" & i) = Sht.Name nSht.Range("B" & i) = cell.Address nSht.Range("C" & i) = cell.Comment.Text nSht.Range("C" & i).WrapText = False nSht.Range("D" & i) = cell.Value i = i + 1 Next cell End If Set Rng = Nothing Next Sht Application.ScreenUpdating = True Application.DisplayAlerts = True MsgBox "All comments are listed in Comment List Sheet!!!", vbInformation End Sub