Cell comments

jeancake

Board Regular
Joined
Nov 3, 2008
Messages
57
I get a file that have numerous cells with comments in column E. I would like to move the comments from their original cells and place them in Column F under a new field called comments. Is there a procedure that can do that?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
How about manually?

Copy col E, select col F, paste special, comments

Select col E again, Clear > Comments
 
Upvote 0
If you want a macro, ...

Code:
Sub x()
    With Range("E:E")
        .Copy
        .Offset(, 1).PasteSpecial Paste:=xlPasteComments
        .ClearComments
    End With
    Application.CutCopyMode = False
End Sub
 
Upvote 0
Not quite what I'm looking for. When I try it your way it gets copied as a comment in Column F, but I would like it to paste it as an actual value in the column as if I had typed it in column F myself.
 
Upvote 0
I think this will work for you:

Sub MoveComments()
On Error Resume Next
Dim rowCounter, place1 As Long
Dim rawComment, modComment As String
Application.ScreenUpdating = False
'Find the last row to use as a counter
rowCounter = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Do Until rowCounter = 1
curComment = ""
ActiveSheet.Range("E" & rowCounter).Select
If IsEmpty(ActiveCell.Comment.Text) = True Then
curComment = ""
modComment = ""
Else
curComment = ActiveCell.Comment.Text
'OPTIONAL - remove the name of the commenter
place1 = InStr(1, curComment, ":")
modComment = Mid(curComment, place1 + 1, Len(curComment) - place1)
''''''''''''''''''''''''''''''''''''''''''''
End If
'Move to the right column and place the comment in the field
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = modComment 'Change modComment to rawComment to include name of commenter
'decrease counter
rowCounter = rowCounter - 1
Loop
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top