Show all Comments in Excel using VBA – ExcelVbaAutomation

Show all Comments in Excel

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.

Show all Comments

Solution

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.

cell address that has the comment

list of all the comments

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 :

store the address of the commented cell

 

Leave a Reply

Your email address will not be published. Required fields are marked *