List all Comments

Tested in Excel 365 (16.8730.2046) 64-bit

Don’t you hate it when you receive a data file with lots of yellow comments that are either hard to spot or collapsed needing resizing? And the worst is that you cannot filter the data using them!

Here is a sub that could help you with this – it would create a new sheet containing a mapping of all comments with hyperlinks to them or a new column in the active sheet containing all comments for each line, separated by “|”.

Optional, you can delete the comments because when there are too many, they tend to disappear or even block the whole file.

 
 
  1. Option Explicit
  2. Sub ListComments(Optional InNewSheet = True, Optional ClearComments = False)
  3.     Const ResultSheet = "List of Comments"
  4.     Dim Cl As Range
  5.     Dim i, LastRow, LastCol
  6.    
  7.    
  8.    
  9.     ToggleExcelFunctionalities (False)
  10.     If InNewSheet Then
  11.         ' delete existing sheet
  12.        On Error Resume Next
  13.         Sheets(ResultSheet).Delete
  14.        
  15.         Sheets.Add After:=Sheets(Sheets.Count)
  16.         ActiveSheet.Name = ResultSheet
  17.         Cells(1, 1).Value = "Sheet"
  18.         Cells(1, 2).Value = "Cell"
  19.         Cells(1, 3).Value = "Comment"
  20.         Cells(1, 4).Value = "Link"
  21.        
  22.         LastRow = 2
  23.         For i = 1 To Sheets.Count - 1
  24.             Sheets(i).Select
  25.             Selection.SpecialCells(xlCellTypeComments).Select
  26.             For Each Cl In Selection
  27.                 With Sheets(ResultSheet)
  28.                     .Cells(LastRow, 1).Value = Sheets(i).Name
  29.                     .Cells(LastRow, 2).Value = Cl.Address
  30.                     .Cells(LastRow, 3).Value = Cl.NoteText
  31.                     .Cells(LastRow, 4).FormulaR1C1 = "=HYPERLINK(""[" & ActiveWorkbook.Name & "]""&RC[-3]&""!""&RC[-2],""Go to"")"
  32.                     ' Optional: Delete comment
  33.                    If ClearComments Then Cl.ClearComments
  34.                     LastRow = LastRow + 1
  35.                 End With
  36.             Next Cl
  37.                
  38.         Next i
  39.     Else
  40.         ' use the current sheet
  41.        LastCol = LastRowOrCol(1, 1, 10) + 1
  42.         Selection.SpecialCells(xlCellTypeComments).Select
  43.         For Each Cl In Selection
  44.             Cells(Cl.Row, LastCol).Value = Cells(Cl.Row, LastCol).Value & Cl.NoteText & "|"
  45.             ' Optional: Delete comment
  46.            If ClearComments Then Cl.ClearComments
  47.         Next Cl
  48.     End If
  49.     On Error GoTo 0
  50.    
  51.    
  52.     ToggleExcelFunctionalities (True)
  53. End Sub

You can use the ToggleExcelFunctionalities from here, and LastRowOrCol from here.

 

Add a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.