Bulk Transferring Comments To Bottom Cells.

[sdfhse]

I am using comments as a sort of diary and the comments are always open and viewable. But after this, I want all the comments to be in the cells. But there are thousands of comments in the past years already. How can I transfer comments to cells? In example, There is a comment writing in A1 cell but I want it in the cell of A2 not as a comment but into the cell itself.... Same is true for B1, C1, D1, .......... All comments in these should be transferred to B2, C2, D2.... not as a comment but into the cells. Is there any way to do this in bulk, for thousands of them at one go?

 

[pbornemeier]

Try this. test on a copy of your worksheet.

Option Explicit

Sub CopyCommentToCellToRightText()

    Dim rngComments As Range
    Dim rngCell As Range
    
    Set rngComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

    'If you want to limit it to only comments in column A uncomment next line
    'Set rngComments = Intersect(rngComments, Columns(1))

    For Each rngCell In rngComments
        rngCell.Offset(0, 1).Value = rngCell.Comment.Text
    Next
    
    'If you want to delete comments uncomment next line
    'rngComments.ClearComments
    
End Sub

 

[sdfhse]

This worked like a charm Thanks!

Instead of right, how can I make it one bottom cell?

 

[pbornemeier]

This will move comment to cell below. I misread your original post.

Option Explicit

Sub CopyCommentToCellToRightText()

    Dim rngComments As Range
    Dim rngCell As Range
    
    Set rngComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

    'If you want to limit it to only comments in column A uncomment next line
    'Set rngComments = Intersect(rngComments, Columns(1))

    For Each rngCell In rngComments
        rngCell.Offset(1, 0).Value = rngCell.Comment.Text
    Next
    
    'If you want to delete comments uncomment next line
    'rngComments.ClearComments
    
End Sub

 

[sdfhse]

Thanks you so much Pbornemeier. All the best.