Fly, Penguin!

I blog so I don't forget.

Excel - convert columns to comments

1 minute read #excel #solved

Situation: You have table rows containing short headings, and the cell next to it a long text description. You want to “convert” the description column into comments on the “heading” cells to make the table smaller or save vertical space. Maybe a two images explain that better.

So we want to go from “before” …

setup screen

… to “after” …

setup screen

Now this is the Excel VBA code to achieve this:

Sub convert_to_comment()
' convert_to_comment Makro
' Tastenkombination: Strg+k
    ' step 1 - create (VISIBLE!) comments
    ' visible because excel has a bug. yeah ... .
    Dim commentText As String
    commentText = ActiveCell.Offset(0, 1).Text
    ActiveCell.AddComment commentText
    ActiveCell.Comment.Visible = True
    ActiveCell.Comment.Shape.Width = 450
    ActiveCell.Comment.Shape.Height = 150
    ActiveCell.Offset(1, 0).Select

    ' make comments invisible in step 2
    ' just comment all lines from step1, and uncomment all of the following
    ' lines, and re-run the macro with each cell. see notes below in the
    ' blog post.
    'ActiveCell.Comment.Visible = False
    'ActiveCell.Offset(1, 0).Select
End Sub

Some notes:

  • Excel does not seem to support auto-sized comments since v2016. Don’t get me started on how much I “dislike” this pile of … whatever.
  • At least Excel v16.55 has a bug which makes this 2-step-approach necessary (otherwise the comment boxes just won’t show).