I need help with writing better performance code for comments formating.
Here is my code:

For Each c In Cells.SpecialCells(xlCellTypeComments)

c.Comment.Visible = True
c.Comment.Shape.Select True
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
.AutoSize = True
.Left = c.Left + c.Width + 3
.Top = c.Top + 2
End With
c.Comment.Visible = False
Next c

In sheet with ~550 comments it takes about 4 minutes.
Do you have any ideas ?

Try turning screenupdating off

Application.ScreenUpdating = False

(reset afterwards), and don't select the shape

With c.Comment.Shape

instead of


c.Comment.Shape.Select True
With Selection


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
I need help with writing better performance code for comments formating.
Here is my code:

For Each c In Cells.SpecialCells(xlCellTypeComments)

c.Comment.Visible = True
c.Comment.Shape.Select True
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
.AutoSize = True
.Left = c.Left + c.Width + 3
.Top = c.Top + 2
End With
c.Comment.Visible = False
Next c

In sheet with ~550 comments it takes about 4 minutes.
Do you have any ideas ?

Bob, thanks for your answer, but
1) screen updating is switched off (I posted only a part of my code)
2) code you recommended (With c.Comment.Shape) unfortunattely doesn't work
(in connection with .PrintObject, .AutoSize, ...)

thanks for your try ...

Vlado

"Bob Phillips" wrote:

Try turning screenupdating off

Application.ScreenUpdating = False

(reset afterwards), and don't select the shape

With c.Comment.Shape

instead of


c.Comment.Shape.Select True
With Selection


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
I need help with writing better performance code for comments formating.
Here is my code:

For Each c In Cells.SpecialCells(xlCellTypeComments)

c.Comment.Visible = True
c.Comment.Shape.Select True
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
.AutoSize = True
.Left = c.Left + c.Width + 3
.Top = c.Top + 2
End With
c.Comment.Visible = False
Next c

In sheet with ~550 comments it takes about 4 minutes.
Do you have any ideas ?

problem is in:
c.Comment.Visible = True

without this line running time is only ~ 1 second

.... but I don't know why ;-(

maybe the reason is that next lines operates with visible comment (?)

So, is it needed?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Vlado Sveda" wrote in message
...
problem is in:
c.Comment.Visible = True

without this line running time is only ~ 1 second

... but I don't know why ;-(

maybe the reason is that next lines operates with visible comment (?)

I think (and hope) it isn't.

Vlado


"Bob Phillips" wrote:

So, is it needed?