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
Comments inserted in cells
-
- Administrator
- Posts: 12615
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Comments inserted in cells
Something like
Replace the Debug.Print with code to put the text wherever you want it
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
StuartR
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Comments inserted in cells
Do you want to place the text of the comments on a new sheet, or on a range of the same sheet?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Comments inserted in cells
Thanks Stuart/Hans,
I want to put it on the range of the same sheet.
regards,
vkkt
I want to put it on the range of the same sheet.
regards,
vkkt
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Comments inserted in cells
The following macro will list all comments from the selected range. If you'd rather specify the range, change the line
The output will be in columns P and Q. If you want it somewhere else, change the line
Macro:
Code: Select all
Set rng = Selection
Code: Select all
Const col = 16
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Comments inserted in cells
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.
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.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Comments inserted in cells
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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Comments inserted in cells
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
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Comments inserted in cells
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Comments inserted in cells
Thanks Hans, Great, its working as required.
regards,
VKKT
regards,
VKKT