How to Quickly Add Comments to Cells in Your Excel Worksheet via VBA

In certain circumstance, you need to copy comments into designated cells. Here we will introduce a quick method to finish this task.

In the image below, you can see there are two comments in the worksheet. And to make the interface cleaner without hiding those comments, you can copy comments into certain cells.

If you do this task manually, you will certainly spend a lot of time, especially when there are many comments. Hence, you may refer to the method below.

Copy Comments into Cells

1. Press the shortcut keys “Alt +F11” on the keyboard to activate the Visual Basic editor.
2. And then click the tab “Insert” in the toolbar.
3. Next choose the option “Module” in the sub menu. Thus, you have inserted a new module into the editor.
4. Now copy the following codes and paste into the module.

Sub CommentIntoCell()

    Dim cel As Range, a As Integer

    a = 21

    For Each cel In Range(“A1:C13”)

        If Not cel.Comment Is Nothing Then

            Cells(a, 1).Value = cel.Address(RowAbsolute:=False, _

            ColumnAbsolute:=False, _

            ReferenceStyle:=xlA1) & “: ” & cel.Comment.Text

            a = a + 1

        End If

    Next

End Sub

In this sub, we will input the first comment into cell C21 in the worksheet, and the second into cell C22. If you need to input comments into other range, you can also modify the codes according to your need. And in the target cells, we will not only copy the comments, but also input the original cells addresses. Therefore, you will be clear about the exact position of the original cells.

5. After that, press the button “F5” on the keyboard to run the sub. In addition, you can also click the button “Run Sub” in the toolbar.

Now you can come back to the worksheet and check the result.

The comments are already appeared in the designated cells. And the cells addresses are also appear in those cells. Then you can either hide those comments or just delete them from the worksheet.

And the next time if you need to copy comments into cells, don’t copy them one by one manually. Using the VBA process can save you a lot of time and energy.

Excel Files can Become Vulnerable

You may have already heard about some malicious virus and malware. The virus and malware can attack your computer and damaged your files. When your computer gets affected, the Excel files will become vulnerable. In order to protect your files from corruption, you can prepare a recovery tool at hand. This tool can repair xlsx data error and solve other complex errors in Excel. Hence, you will never need to worry about your precious files.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx file corruption and outlook repair software products. For more information visit www.datanumen.com