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.
'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
i = newwks.Cells(Rows.Count, 1).End(xlUp).Row
For Each mycell In commrange
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
Set commrange = Nothing
'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, _
Application.ScreenUpdating = True
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).
Ads by Google
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.