Get a List of All the Comments in a Worksheet in Excel

If you work with Excel worksheets that have a lot of cell comments in a worksheet, this tutorial could be useful. Sometimes you may insert cell comments to highlight or explain data, or your boss may insert comments in cells while reviewing your work.

If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list.

Get a List of All the Comments in a Worksheet

In this tutorial, I will show you how to use a VBA code to get a list of all the comments in a Worksheet in a separate worksheet.

Something as shown below:

Get a List of All the Comments in a worksheet in Excel - Demo

There is a VBA code in the backend that does the following things:

  • It checks whether there are any comments in the active worksheet or not. If there are no comments, it quietly bows out and does nothing.
  • If there are comments in the worksheet, it creates a new worksheet (“Comments”) and extract a list of all the comments in the following structure:
    • Column A has the cell address that has the comment.
    • Column B has the commenter name. This comes in handy if there are multiple reviewers of the same file. It will also help filter/sort based on reviewers name.
    • Column C has the comment.

Download the Example File
Download File

The VBA Code

Here is the VBA code that does all the heavy lifting here.

Sub ExtractComments()
Dim ExComment As Comment
Dim i As Integer
Dim ws As Worksheet
Dim CS As Worksheet
Set CS = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each ws In Worksheets
  If ws.Name = "Comments" Then i = 1
Next ws
    
If i = 0 Then
  Set ws = Worksheets.Add(After:=ActiveSheet)
  ws.Name = "Comments"
Else: Set ws = Worksheets("Comments")
End If

For Each ExComment In CS.Comments
  ws.Range("A1").Value = "Comment In"
  ws.Range("B1").Value = "Comment By"
  ws.Range("C1").Value = "Comment"
  With ws.Range("A1:C1")
    .Font.Bold = True
    .Interior.Color = RGB(189, 215, 238)
    .Columns.ColumnWidth = 20
  End With
  If ws.Range("A2") = "" Then
    ws.Range("A2").Value = ExComment.Parent.Address
    ws.Range("B2").Value = Left(ExComment.Text, InStr(1, ExComment.Text, ":") - 1)
    ws.Range("C2").Value = Right(ExComment.Text, Len(ExComment.Text) - InStr(1, ExComment.Text, ":"))
  Else
    ws.Range("A1").End(xlDown).Offset(1, 0) = ExComment.Parent.Address
    ws.Range("B1").End(xlDown).Offset(1, 0) = Left(ExComment.Text, InStr(1, ExComment.Text, ":") - 1)
    ws.Range("C1").End(xlDown).Offset(1, 0) = Right(ExComment.Text, Len(ExComment.Text) - InStr(1, ExComment.Text, ":"))
  End If
Next ExComment
End Sub

How to Use this Code

There are couple of ways you can use this code to get a list of comments from your worksheet:

#1 Copy Paste Data in the Example File

Copy paste your data (as is) in the Data tab of the download file and then run this macro.

To run this:

  • Go to the Developer tab and click on Macros. It will open the Macro dialogue box.Get a List of All the Comments in a worksheet in Excel - Macro Button
  • Select ExtractComment and click on Run. Make sure you are in the sheet that has the comments that you wish to extract.Get a List of All the Comments in a worksheet in Excel - Run Macro
#2 Copy Paste the Code in Your Workbook

Copy the code and paste it in the workbook from which you want to extract the comments.

To do this:

  • Activate the workbook in which you are working and press Alt + F11. This will open the VB Editor window.Get a List of All the Comments in a worksheet in Excel - VBE
  • In the Project Explorer on the left, right-click on any of the objects for that workbook, go to Insert –> Module.Get a List of All the Comments in a worksheet in Excel - Insert Module
  • Paste the code in the Module code window.
  • Close the VB Editor Window (or press Alt + F11 to go back to the worksheet).

Now you have the macro in your workbook. To run the macro, go to Developer Tab –> Macros. In the Macro dialogue box, select the ExtractComment macro and click in Run.

Note: Make sure you save the workbook with .XLS or .XLSM extension.

#3 Create Add-in

If you need to use this code often, it’s best to create an add-in out of it. That way you can easily use it in any workbook (without the additional effort of copy pasting the code again and again).

Here is how to create an Add-in:

  • Go to File –> Save As.Get a List of All the Comments in a worksheet in Excel - Save As
  • In the Save As dialogue box, change the Save as type to .xlam.
    • You’ll notice that the path of the file where it gets saved automatically changes. You can change it if you want.Get a List of All the Comments in a worksheet in Excel - Save as xlam
  • Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.Get a List of All the Comments in a worksheet in Excel - Addin
  • In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.Get a List of All the Comments in a worksheet in Excel - Activate Addin

Once an add-in has been activated, you can use it in any workbook. To do that, go to Developer –> Macros. In the Macro dialogue box, select the ExtractComment macro and run it.

Download the Example File
Download File

Hope this code will save you some time. Let me know your thoughts in the comments section.

If you work with Excel, VBA could be a powerful ally. Take your Excel Skills to the next level with the VBA JetPack Course.

You May Also Like the Following Excel Tutorials:

  • Ganesh Karra says:

    In case there is a picture for the comment Box, how to get the same in another column

  • Guy Harvey says:

    Is there a way of including the first column cell data for the comment address? This

  • Deepak says:

    is it possible without VBA ?

  • Frank Tonsen says:

    You might prefer using the property: ExComment.Author

  • >