Comments inserted in cells

[VKKT]

Greetings:
Hi All,

I have an old excel table which has comments inserted in most of the cells, is it possible to extract all these comments and copy to a new cell.

regards,
VKKT

[StuartR]

Something like

Code: Select all

    Dim cmnt As Comment
    Dim allComments As String
    For Each cmnt In ActiveSheet.Comments
        allComments = allComments & cmnt.Text & vbCrLf
    Next cmnt
    Debug.Print allComments
    

Replace the Debug.Print with code to put the text wherever you want it

[HansV]

Do you want to place the text of the comments on a new sheet, or on a range of the same sheet?

[VKKT]

Thanks Stuart/Hans,

I want to put it on the range of the same sheet.

regards,
vkkt

[HansV]

The following macro will list all comments from the selected range. If you'd rather specify the range, change the line

Code: Select all

    Set rng = Selection

The output will be in columns P and Q. If you want it somewhere else, change the line

Code: Select all

    Const col = 16

Macro:

Code: Select all

Sub ListComments()
    Const col = 16 ' number of output column
    Dim rng As Range
    Dim cel As Range
    Dim r As Long
    Application.ScreenUpdating = False
    Cells(1, col).Value = "Cell"
    Cells(1, col + 1).Value = "Comment"
    r = 1
    Set rng = Selection ' or a specific range
    For Each cel In rng
        If Not cel.Comment Is Nothing Then
            r = r + 1
            Cells(r, col).Value = cel.Address(False, False)
            Cells(r, col + 1).Value = cel.Comment.Text
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

[VKKT]

Thanks Mr. Hans,

It is working well, but if there are no comments in one cell it is not leaving the cell blank against it but copying the comments of the below cell in this cell. How to solve this?

regards,
VKKT.

[HansV]

I don;t understand. In the screenshot below, there are 5 cells with comments, and the code filled 5 rows in columns P and Q:

S1419.png

[VKKT]

I want the copy of comments against the same cell which has comments inserted.
In your example the comments of C6(cell 4) is shown in Q5, I need it in Q6 and Q5 should be blank.

Hope it is clear.
Regards,
VKKT

[HansV]

Try this version then:

Code: Select all

Sub ListComments()
    Const col = 16 ' column offset
    Dim rng As Range
    Dim cel As Range
    Application.ScreenUpdating = False
    Set rng = Selection ' or a specific range
    For Each cel In rng
        If Not cel.Comment Is Nothing Then
            cel.Offset(0, col).Value = cel.Comment.Text
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

[VKKT]

Thanks Hans, Great, its working as required.
regards,
VKKT