Dynamic Cell Range for Comments

[healey21]

I have been asked if it is possible to add a cell comment with a list from a range of cells, the range will vary in size.

I have written this so far, but it only adds the next cell content not all the cells that has content in column F (only expecting around 15 items max).

Sub tryme()
'Healey21 looking to use dynamic list for comments
'Purpose to 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

 

[GlennUK]

What version of Excel please?

 

[healey21]

We are using 2007, thank you for reading this thread.

 

[healey21]

Sorry to request this, but does anyone have any ideas to place a range into a comment. To extend my current code.

 

[JackDanIce]

I'm not sure I understand your request. Do you want the contents of the range of cells in column F (from F2 down to last) as your comment in N2? Or do you want the actual address of the range of cells from F2 down?

If it's the contents then use:

Sub tryme()
 
Dim i as Long
Dim msg as string
For i = 2 to Range("F" & Rows.Count).End(xlUp).end
   msg = msg & Range("F" & i) & Chr(10)
Next i
msg = Trim(msg)
 
With Range("N2")
  .ClearComments
  .AddComment
  .Comment.Text msg
End With
 
End Sub

If it's the range address itself then:

Sub tryme()
 
Dim i as Long
Dim msg as String
 
i = Range("F" & Rows.Count).End(xlUp).Row
msg = Range("F2").Address & ":" Range("F" & i).Address
 
With Range("N2")
  .ClearComments
  .AddComment
  .Comment.Text msg
End With
 
End Sub

 

[healey21]

Thank you for the reply, it is about the contents of the cells, rather than cell address. Your first example looks really good, but it fails on the line which has .End and it states argument not optional.

The other does give me the cell address and works.

Sub tryme()

Dim i As Long
Dim msg As String
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

End Sub

 

[JackDanIce]

Sorry, typo, try:

Sub tryme()
 
Dim i as Long
Dim msg as string
For i = 2 to Range("F" & Rows.Count).End(xlUp).Row
   msg = msg & Range("F" & i) & Chr(10)
Next i
msg = Trim(msg)
 
With Range("N2")
  .ClearComments
  .AddComment
  .Comment.Text msg
End With
 
End Sub

 

[healey21]

Oh yes, that is exactly what I need, JackDanIce thank you

 

[JackDanIce]

You're welcome