How to Extract Comments in an Excel Sheet

Comment boxes are great when you want to share additional information about Excel data. You can view all comments on a worksheet or just specific comments you want to see.  However, there may be times when you need to consolidate the comments in an Excel worksheet. In this post, we show you how to extract comments in an Excel Sheet using a macro.

If you have never created a macro before, you can learn how to do it in an easy way, without writing the codes yourself, using a macro recorder. You can read more about how to do that here.

HOW TO EXTRACT COMMENTS IN AN EXCEL SHEET

To extract comments in an Excel sheet and move it to another worksheet you need to use a User defined function. That is, custom functions that you can create yourself.

1. To get started, copy this code.

Sub ShowCommentsAllSheets()
 'modified from code
 'posted by Dave Peterson 2003-05-16
 Application.ScreenUpdating = False
 
 Dim commrange As Range
 Dim mycell As Range
 Dim ws As Worksheet
 Dim newwks As Worksheet
 Dim i As Long
 
 Set newwks = Worksheets.Add
 
 newwks.Range("A1:E1").Value = _
 Array("Sheet", "Address", "Name", "Value", "Comment")
 
 For Each ws In ActiveWorkbook.Worksheets
 On Error Resume Next
 Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
 On Error GoTo 0
 
 If commrange Is Nothing Then
 'do nothing
 Else
 
 i = newwks.Cells(Rows.Count, 1).End(xlUp).Row
 
 For Each mycell In commrange
 With newwks
 i = i + 1
 On Error Resume Next
 .Cells(i, 1).Value = ws.Name
 .Cells(i, 2).Value = mycell.Address
 .Cells(i, 3).Value = mycell.Name.Name
 .Cells(i, 4).Value = mycell.Value
 .Cells(i, 5).Value = mycell.Comment.text
 End With
 Next mycell
 End If
 Set commrange = Nothing
 Next ws
 
 'format cells for no wrapping, remove line break
 newwks.Cells.WrapText = False
 newwks.Columns("E:E").Replace What:=Chr(10), _
 Replacement:=" ", LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False, _
 SearchFormat:=False, ReplaceFormat:=False
 
 Application.ScreenUpdating = True
 
 End Sub

2. Open the Excel 2010 worksheet containing the data with comments.

3. Press Alt + F11 to enter the Visual Basic Editor.

4. If necessary, press Ctrl + R to show the Project Explorer.

5. Right-click desired file on left (in bold).

6. Choose Insert -> Module.

7. Paste the code into the right pane.

8. Press Alt + Q to close the VBE.

9. Save the workbook in the XLSM format.

10. Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.

11. On the Developer tab, in the Code group, click Macros.

12. In the Macro dialog box, select the macro and click Run. The comments in your datasheet will be extracted to a new sheet. You can then continue your analysis or other tasks associated with the extracted data.