Need a little help with my (working) code regarding putting headers into comments

[markpem]

Hello

I currently have the following code (which works a treat) and it puts all the data in A2, B2, C2, D2 etc into a comment in A2.

However I also have headers in A1, B1, C1, and I would like them adding like the screenshot if anyone could give me the code?

Thanks!

Current Code:-

Dim cmt As Comment 
Dim rng As Range 
 
Set rng = Range("A1") 
 
Do 
    Set cmt = rng.Comment 
     
    If cmt Is Nothing Then 
        rng.AddComment 
        Set cmt = rng.Comment 
    End If 
     
    cmt.Text Join(Application.Transpose(Application.Transpose(rng.Resize(, 6).Value)), Chr(10)) 
     
    cmt.Visible = True ' optional
     
    Set rng = rng.Offset(1) 
Loop Until rng.Value = ""

Click on picture:-
comment_picture.jpg

[Aflatoon]

Your code doesn't quite match your picture but it will be something like:

    Dim cmt                   As Comment    Dim rng                   As Range
    Dim n                     As Long
    Dim sComment              As String


    Set rng = Range("A1")


    Do
        Set cmt = rng.Comment


        If cmt Is Nothing Then
            rng.AddComment
            Set cmt = rng.Comment
        End If
        For n = 1 To 6
            sComment = sComment & vbLf & Cells(1, n).Value & ": " & Cells(rng.Row, n).Value
        Next n
        cmt.Text Mid$(sComment, 2)


        cmt.Visible = True    ' optional


        Set rng = rng.Offset(1)
    Loop Until rng.Value = ""

[mancubus]

hi.

i would include below line in the loop as well...

cmt.Shape.TextFrame.AutoSize = True

[markpem]

Hello Aflatoon

Thankyou for your help, it's 99% working. However what is happening is this (I have taken a picture to explain it a lot better);-
(it should not be copying the previous data)

comment_picture2.jpg

[markpem]

Hello Mancubus

I have done this and the formatting is much better now, thanks agian for your input and help.

hi.

i would include below line in the loop as well...

cmt.Shape.TextFrame.AutoSize = True

[Aflatoon]

Sorry that was silly. Add this after the Do line:

sComment = vbNullstring