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.
- Option Explicit
- Sub ListComments(Optional InNewSheet = True, Optional ClearComments = False)
- Const ResultSheet = "List of Comments"
- Dim Cl As Range
- Dim i, LastRow, LastCol
- ToggleExcelFunctionalities (False)
- If InNewSheet Then
- ' delete existing sheet
- On Error Resume Next
- Sheets(ResultSheet).Delete
- Sheets.Add After:=Sheets(Sheets.Count)
- ActiveSheet.Name = ResultSheet
- Cells(1, 1).Value = "Sheet"
- Cells(1, 2).Value = "Cell"
- Cells(1, 3).Value = "Comment"
- Cells(1, 4).Value = "Link"
- LastRow = 2
- For i = 1 To Sheets.Count - 1
- Sheets(i).Select
- Selection.SpecialCells(xlCellTypeComments).Select
- For Each Cl In Selection
- With Sheets(ResultSheet)
- .Cells(LastRow, 1).Value = Sheets(i).Name
- .Cells(LastRow, 2).Value = Cl.Address
- .Cells(LastRow, 3).Value = Cl.NoteText
- .Cells(LastRow, 4).FormulaR1C1 = "=HYPERLINK(""[" & ActiveWorkbook.Name & "]""&RC[-3]&""!""&RC[-2],""Go to"")"
- ' Optional: Delete comment
- If ClearComments Then Cl.ClearComments
- LastRow = LastRow + 1
- End With
- Next Cl
- Next i
- Else
- ' use the current sheet
- LastCol = LastRowOrCol(1, 1, 10) + 1
- Selection.SpecialCells(xlCellTypeComments).Select
- For Each Cl In Selection
- Cells(Cl.Row, LastCol).Value = Cells(Cl.Row, LastCol).Value & Cl.NoteText & "|"
- ' Optional: Delete comment
- If ClearComments Then Cl.ClearComments
- Next Cl
- End If
- On Error GoTo 0
- ToggleExcelFunctionalities (True)
- End Sub
You can use the ToggleExcelFunctionalities from here, and LastRowOrCol from here.