comment autosize help

[Ago]

how can i merge these two loops in to one?

[VBA]
For k = i To i + j - 1
For l = i To i + j - 1
If k <> l Then
Range("G" & k).comment.Text Text:=Range("G" & k).comment.Text & _
Chr(10) & Range("E" & l).Value & _
" " & Range("L" & l).Value
End If
Next l
Next k
i = i + j - 1

Set myRng = Range("G6", "G" & n1) 'sets the range in myRng

Application.StatusBar = "Adjusting the size of the comments"
'below it sets the size on each comment to autosize
For Each mycell In myRng.Cells
If Not (mycell.comment Is Nothing) Then
With mycell.comment
.Shape.TextFrame.AutoSize = True
End With
End If
Next mycell[/VBA]

in the upper loop i tried: Range("G" & k).Comment.Shape.TextFrame.AutoSize = True
but it doesnt work.

it feels so stupid to have a code that first creates the comment then leaves it, and a few seconds later it has to find the comment again and adjust the size of the comment.

[Bob Phillips]

This seems to work fine for me

[vba]

For k = i To i + j - 1
For l = i To i + j - 1
If k <> l Then
With Range("G" & k).Comment
.Text Text:=Range("G" & k).Comment.Text & _
Chr(10) & Range("E" & l).Value & _
" " & Range("L" & l).Value
.Shape.TextFrame.AutoSize = True
End With
End If
Next l
Next k
[/vba]

[Ago]

offcourse!
thanks alot!

the strange thing is that the code is much slower now than it was before the change.

first i noticed that it autosized every time it made a new entry in the comment so i moved the with-autosize part to between next l and next k.

but it still was about one minute slower.
strange.

without the change it takes about 10 seconds to do the whole script, after 1 minute +