http://exceltips.vitalnews.com/Pages..._to_Cells.html
gives this code
Sub CommentsToCells()
Dim rCell As Excel.Range
Dim rData As Excel.Range
Dim sComment As String
' Horizontal displacement
Const iColOffset As Integer = 1 ' change value to how many cells to the right you need to go
' extract comments from selected range
If TypeName(Selection) = "Range" Then
Set rData = Intersect(Selection, ActiveSheet.UsedRange)
For Each rCell In rData.Cells
On Error Resume Next
sComment = rCell.Comment.Text
If Len(sComment) > 0 Then
rCell.Offset(, iColOffset).Value = sComment
rCell.Comment.Delete
End If
sComment = ""
On Error GoTo 0
Next
End If
End Sub
just tried it and it works a treat
just select all cells in the column with comments as a contiguous range,and run code
Bookmarks