VBA Copy comments to a Range in Excel
If a worksheet is dotted with lot of comments and you want to respond to each one of these, it is better to extract the comments and place it in a column. This would help in responding to the comments. The following code will extract all comments and place it in column ‘F’ along with the value of the original cell
Sub Convert_Comment_To_Text()
Dim oCom As Comment
Dim sVal As String
Dim i As Integer
For i = 1 To ActiveSheet.Comments.Count
sVal = ""
Set oCom = ActiveSheet.Comments(i)
sVal = ActiveSheet.Cells(oCom.Parent.Row, oCom.Parent.Column).Value
ActiveSheet.Range("F" & CStr(oCom.Parent.Row)).Value = "'" & sVal & " -- " & oCom.Text
Next
End Sub
Before Running the Macro
Comments placed in Column FSee also:
Delete Comments from Excel Workbook using VBA
Posts
