PDA

View Full Version : Replace all comments



entwined
02-23-2012, 08:15 PM
Hi VB'ers,

I need a macro that will replace all the comments in an excel sheet with one comment only. It is just in one column. Currently the comments there are inconsistent so I want the texts of all the comments in that column to be exactly the same. Any help will be very much appreciated. Thanks...

raji2678
02-23-2012, 10:25 PM
Hope this helps

Sub Button4_Click()
Dim i As Integer
Dim cmt As Comment
For i = 1 To 100 'assume there are 100 rows
For j = 1 To 100 'assume there are 100 cols
Set cmt = ActiveSheet.Cells(i, j).Comment
If Not cmt Is Nothing Then
cmt.Delete
cmt.Parent.AddComment Text:="new comment"
End If
Next j
Next i

mohanvijay
02-24-2012, 01:11 AM
Try this



Dim O_Com As Comment
Dim T_Com As String
For Each O_Com In ActiveSheet.Comments
T_Com = T_Com & O_Com.Text & vbCrLf
Next

'''T_Com has the consolidated text of all comments

Set O_Com = Nothing