Sorting out Excel Comments

The following Macro reformats the comments in  a spreadsheet when the Excel bug has messed them all up …

Sub AutosizeComments()
 ' Alt F11 + Insert - Module
 ' Insert this macro then close the VBA
 ' then alt F8 to run and select the AutoSizeComments option
 ' moves the comments to rght by where you need them, adjust width and Heght as you see fit
 Dim cmt As Comment, cell As Range
 On Error Resume Next
 For Each cell In ActiveSheet.UsedRange
 Set cmt = cell.Comment
 If Not cmt Is Nothing Then
 cmt.Shape.Placement = xlMove
 cmt.Shape.Left = cell.Left + cell.Width + 30
 cmt.Shape.Top = cell.Top + 10
 cmt.Shape.Width = 200
 cmt.Shape.Height = 150
 cmt.Shape.Left = c.Offset(0, 1).Left
 cmt.Shape.Top = c.Offset(-1, 1).Top
 End If
 Next cell
 End Sub

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s