set a selection of SpecialCells(xlCellTypeComments) issues

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
I have a large selection of excel comments. I am trying to set a range of comments to xlMoveandSize. The problem is that excel is not letting set a range of cells, SpecialCells(xlCellTypeComments), properties all at once. The only way I can find to do this is by doing a 'for each loop.

That takes way too long!!

Any other suggestions how to set a range of cells comment property to be xlmoveandsize?

Obviously I am doing this through VBA.

I am relying on this board to help me out. Research has been unsuccessful to get the answer.

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I am trying to do this, see below, except for a range of cells.

Range("A1").Comment.Shape.Placement = xlMoveandSize

because I later sometimes hide rows that have comments on them and I get the error that you can't move objects off a sheet.
 
Upvote 0
This is exactly what I am doing.

I am in a loop going through ~2500 rows expanding about 25 columns across. I am inserting comments into certain cells, based on some criteria.

This is what i do

DataCell.AddComment MID(AvailText, 2) 'AvailText is from another sheet)
DataCell.Comment.Shape.Placement = xlMoveAndSize

When you do this many times (hundreds of times) it takes approx 8-14 minutes to do this.

If you comment out DataCell.Comment.Shape.Placement = xlMoveAndSize this line it only takes 30 seconds to update everything. I am trying to do set the .Comment.Shape.Placement = xlMoveAndSize on all comments at once instead of setting this property one at a time. [/code]
 
Upvote 0
Thanks VoG II,
However I have tried that and it takes the same about of time (8-14 minutes) to loop through each comment and set the shape.placement = xlmoveandsize

I am trying to set the shape.placement of all cells on a sheet that have comments to be = xlmoveandsize all at once.

Looping through each comment takes wayyy to long
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top