- Excel - convert columns to comments

Excel - convert columns to comments

Posted on December 10, 2021 (Last modified on October 23, 2024) • 1 min read • 204 words

Share via

Link copied to clipboard

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
    ActiveCell.Comment.Delete
    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).

Location of Mac "Quick Actions"

Migrating Postgres databases in containers

In case you want to follow me

Here are some links. The further to the right, the less active.