Resizing Multiple Comments

[holliday50]

I have a spreadsheet with over 1000 rows, with comments in 1 cell in each row. The problem is that the comment box is too small, and cuts off part of the verbiage. How can I resize all of them at once, so that everything in the comment field is visible?

 

[VoG]

Try this:

Sub AutosizeComments()
    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.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

 

[rashiki]

Try this:

Sub AutosizeComments()
    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.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

You're a genius! Thanks!

 

[edelmefry5]

I am quite experienced in Excel, but not real macro savvy. Instructions were clear, precise and worked brilliantly! Can't thank you enough, never found an answer before! Was so impressed I actually registered on this website just to Thank you both. Also good to know it is a bug and not anything I am in advertently doing wrong.

 

[lauravstuart]

Thank you so much for putting up such simple and concise instructions on a workaround!!! I also registered on this site just so that I could thank you!

 

[HTR1969]

You're a genius! Thanks!

Ditto that!! Thanks very much for the clear and precise instructions. Much appreciated.

 

[lordway]

This works great, however, I have thumbnail pictures in my comments and when I ran this macro it resized the comment boxes smaller to only fit the user name who added the comment and not the picture. I would like a similar macro that allowes me to set the thumbnail picture size to something larger that the comment box default.

Thanks in advance to anyone who can help with a similar/modified macro!

Try this:

Sub AutosizeComments()
    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.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

 

[adunbar]

Let me echo what others have said. I registered just so I could say thank you. I have struggled with this issue for years, and you made it so easy to solve. THANK YOU!

 

[hoohoolian]

OK, VoG, that works as stated. However, this does not wrap text. My comments are very long, sometimes 100 words, and the comments are now unreadable, reaching absurdly far off the page. More importantly, my issue with this bug is in editing comments. Example: I am at row 10, column AD; hovering, the comment appears next to that box. When I click edit comment, the box opens at row 1447, column A (this is consistent for any comment in the same column, but each column gets sent to different areas, e.g ). I cannot move the edit-comment-box further than the visual page. I must either copy the content, delete comment and re-insert comment -- OR -- reduce the view to 1% and slide, slide, slide -- then open view back to 100%.

Try this:

Sub AutosizeComments()
    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.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

 

[john jenkins]

I've the same issue. The only work around I've found is tedious. It involves placing a period (.) in the lower right corner of the comment to fool Excel into resizing the comment based on text that is only spaces and the final period. First size the comment to fit the image. Then edit the comment using the return key to move the cursor down to the bottom of the comment then space all the way over to the right side and enter a period. The period will show up on your image, but you probably won't notice it unless you're looking. When you run the macro, Excel will resize based on the text which now fits your image.

This works great, however, I have thumbnail pictures in my comments and when I ran this macro it resized the comment boxes smaller to only fit the user name who added the comment and not the picture. I would like a similar macro that allowes me to set the thumbnail picture size to something larger that the comment box default.

Thanks in advance to anyone who can help with a similar/modified macro!