Show all Comments in Excel using VBA – ExcelVbaAutomation

Show all Comments in Excel
  • Save

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
  • Save


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
  • Save

list of all the comments
  • Save

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
        '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
  • Save


Leave a Reply

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

Copy link
Powered by Social Snap