Comment Box - Fill with Range

[Trevor G]

I have the following code to create a comment box in a cell, but what I am after is for it to be filled with a range of cells, currently it will only add 2 cell contents.

Sub tryme()
'looking to use dynamic list for comments
'fill the comment with range of cells which will vary in size
Range("n2").ClearComments
Range("n2").AddComment
Range("N2").Comment.Text Range("F2").Value & Chr(10) & Range("F2").Offset(1, 0).Text
End Sub

 

[JamesMcS]

Happy 1200th post

Would you have to cycle through each cell in the range you want, adding the contents to a string as you go, then put the string in as the comment?

 

[Trevor G]

Hi James.

Only 1200 post, not so bad. Just rocketed passed that by 1 now.

I was thinking that some kind of long to count and string but still misisng it slightly.

I've declared

Dim i As Long
Dim msg As String

And added this

For i = 2 To Range("A" & Rows.Count).End(xlUp).End
msg = msg & Range("A" & i) & Chr(10)
Next i
msg = Trim(msg)

With Range("N2")
.ClearComments
.AddComment
.Comment.Text msg
End With

But it fails on the .End and states its not optional when I test it.

 

[Trevor G]

Just realised I needed .Row not .End.

Working nicely. Complete code is shown here incase anyone else needs it.

Sub CommentAddRange1()

Dim i As Long
Dim msg As String
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
msg = msg & Range("A" & i) & Chr(10)
Next i
msg = Trim(msg)

With Range("N2")
.ClearComments
.AddComment
.Comment.Text msg
End With

End Sub