Delete blank comments in a selected range

[bmk207]

I have a spread sheet with the below VBA code to take the values in the selected cells and turn them into comments on the same cells then fit the comments to there values. I would like to edit it to also delete or not create comments on cells whom's equations return no value. I did not create this VBA as I do not know how. I combined responses from this forum to create the functionality I was desiring. If anyone can help I would appreciate it. Thank you!

Sub ValueToComment()
Dim rCell As Range
For Each rCell In Selection
With rCell
If .HasFormula Then
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.AddComment
.Comment.Text Text:=CStr(rCell.Value)
End If
End With
Next
Set rCell = Nothing

Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

[Bernie Deitrick]

Please Login or Register  to view this content.

[bmk207]

Thank you. Worked great!

[bmk207]

I would like to modify this to pull the values from a sheet named comments on the range B2:E54 and write the values into the comments on a sheet named numbers with the range of B2:E54. Please let me know if this is possible and how if you know how. Thank you!

[Bernie Deitrick]

Try this:

Please Login or Register  to view this content.

[bmk207]

Thank you. Worked Great!