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:
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.
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 a 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.
- Select ExtractComment and click on Run. Make sure you are in the sheet that has the comments that you wish to extract.
#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.
- In the Project Explorer on the left, right-click on any of the objects for that workbook, go to 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 an 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.
- 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.
- Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.
- In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.
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.
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 Excel VBA Course.
You May Also Like the Following Excel Tutorials: