Saturday, September 06, 2008

Convert Excel Comments to Text using VBA

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


End Sub

Before Running the Macro
Comments placed in Column F

See also:

Delete Comments from Excel Workbook using VBA


  1. Anonymous6:26 AM

    Belive that
    oCom = ActiveSheet.Comments(i)
    SHOULD be
    Set oCom = ActiveSheet.Comments(i)

  2. Thanks for your comment. I have corrected it

  3. Thanks for the tip, will be great help if you can modify to put the comments in the new sheet.

  4. Thanks for the tips, it will be great to have the comments in the new sheet. Hope you can add this feature.

  5. Anonymous9:55 AM

    I understand some programming, but I'm not sure what I need to change to make this work. What would the code be if you have comments listed in multiple columns?

  6. It should work for multiple columns as it is extracting from ActiveSheet. Do you mean extracting from Multiple sheets?

  7. Anonymous2:34 AM

    Thanks a lot pal!

  8. Anonymous1:34 AM

    Hi Thanks this is brilliant. I received a spredsheet from a busines partner who had add 20 lines of comments!!!

    The app worked perfectly and saved me a lot of time.

    Thanks a lot!

  9. Anonymous10:18 AM

    How can I convert a column of text to a column of comments? In other words, each entry in the column is converted to a comment..

    Thanks You

  10. Anonymous6:00 AM

    Thank you. This will greatly help me. Because I need to translate text in comments to another language. But could you now write this macro vice versa? How can I get the text from column E back to the comments in column C now?

  11. Here you have it -

  12. Anonymous6:04 PM

    Is there a way to select an individual cell, copy its comment inside brackets at the end of the cell, and delete the comment?

    And is there a way to extract text from within brackets a the end of a cell and rewrite it as a comment/ (reversing the earlier process?)

    I am finding comments incredibly hard and time consuming to read in their current format.

  13. Anonymous7:00 PM

    This is easier. It sends comments from cells in column 1 to corresponding cells in column 2. Just insert a blank column as column 2, then run macro.

    Sub CopyCommentText()
    Dim CmtText As String
    Dim CmtCount As Integer, i As Integer
    Dim CommCell As String

    CmtCount = ActiveSheet.Comments.Count
    For i = 1 To CmtCount
    CommCell = ActiveSheet.Comments(i).Parent.Address
    Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
    Next i

  14. What if there are comments in multiple columns. For instance in your example... what if you had commented on both Price and Availability for Apples? This macro seems to choose only one of the two comments to extract. Any work around for this?

  15. Anonymous3:26 PM

    I have the same question as the user who commented at 10:31AM

  16. do you know how to parse each line of a comment separately? I tried split(mycomment.text,vbcrlf) and vbcr and vblf without any luck, I still get the whole comment as a multi-line string. Many thanks!

  17. Anonymous6:43 AM

    My table range is A:E. In some rows there a more than one comment in a row. Your VBA copies only the last comment in "F".
    How could i change it to get all comments in a row in column "F"?
    Thanks in advance


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.