Table of Contents
Show all Comments in Excel
Comments can be a great way to add a small bit of documentation to our spreadsheets. If we have a huge data set and the comments are scattered all over the worksheet. Here we will show you an easy way to show all Comments in Excel in a workbook.
Below excel sheet contain comments.
1. Now go to developer tab click on insert then select command button active X Control
2. Double click on a button and write down the below code
This code in does the following things:
It checks whether there are any comments in the Particular sheet or not. If there are no comments, it does not do anything
If there are comments in the worksheet, it creates a new worksheet (“Comments”) and extracts a list of all the comments in the following structure:
Column B contains a comment. (we have to take only commented contents & excluded commenter name)
Column A contains the cell address that has the comment.
Private Sub CommandButton1_Click()
Dim i As Integer
Dim cell As Range
i = 2
On Error Resume Next
'To check sheet have comments or not
If Worksheets("Sheet1").Comments.Count = 0 Then
'To add a new sheet at the end of worksheets
'To rename the new added sheet to comments
ActiveSheet.Name = "Comments"
'To loop through each cell that contain comments
For Each cell In Worksheets("sheet1").Cells.SpecialCells(xlCellTypeComments)
'To store the commnets in comments sheet
Worksheets("Comments").Range("A" & i).Value = Right(cell.Comment.Text, Len(cell.Comment.Text) - InStr(cell.Comment.Text, ":"))
'To store the address of the commented cell
Worksheets("Comments").Range("B" & i).Value = cell.Address
i = i + 1
The output will like this :