Share via

Notes flattened to line. Can't grab grips to resize.

Anonymous
2025-07-01T13:57:52+00:00

I have a spreadsheet where the notes have been flatten to only showing a leader line, and I can't grab the grips. I found a suggested macro, but it was one continueous line of code with no returns.

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 End If Next cell End Sub

It doesn't run for me, but I may not have put the line returns in the correct place. It seems to hang on the "end if." What do I need to adjust?

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

End If

Next cell

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-07-01T14:35:22+00:00

Hi Brad,

Thank you for posting in Microsoft Community.

Regarding to your description and based on my understanding, the reason that not running is a syntax error in VBA.

To clarify: 

In VBA, an IF statement can be written in two ways:

  1. Single line IF:
    If condition Then action End If
  • (e.g., If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True)
  1. Multi-line IF: With Then on the first line, followed by the action on subsequent lines, and ending with End If.

Your code combines these two formats: If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True and then immediately End If. This may create a syntax error that stops the code from running correctly. Therefore, I suggest a corrected version of the macro:

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

Additional note: If, after running the macro, you still can't grab the grips or resize a specific comment, it may be extremely small or positioned off-screen. Try zooming out significantly or using Go To Special feature (F5 > Special > Objects) to locate and adjust it.

I hope this helps. Please dont hesistate t let me know if you have any furterh question or concern.

Best Regards,
Mia - MSFT | Microsoft Community Support Specialist.

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-07-01T15:05:34+00:00

    Hi Brad,

    I'm glad the information I provided was helpful to you, and I hope the resources you found will continue to support you in the future.

    If you need any further assistance, please don’t hesitate to post back.

    Best Regards,
    Mia - MSFT | Microsoft Community Support Specialist.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-07-01T14:51:11+00:00

    Works great. Thank you!

    Also thanks for the information about if then statements in VBA. I have no formal VBA training, but have learned and continue to learn from this forum.

    Was this answer helpful?

    0 comments