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 Exit Sub Else 'To add a new sheet at the end of worksheets Sheets.Add after:=Sheets(Sheets.Count) '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 Next cell End If End Sub
The output will like this :