Solved: How to add comments to a entire column

[rafi_07max]

I know that I can add comments to a specific cell by right clicking the cell and click insert comments.


But now for e.g. cell A1 contains the word “apple”, I want to remove the word from cell and make it like comment so that when I move mouse cursor onto cell A1 the word ‘apple’ appears.


I want to do this for the entire column.


Sample Pictures

Before:

http://www.iimmgg.com/image/a4a62ae7...156913c8db94b5

After:

http://www.iimmgg.com/image/f0633d71...454529b4d193f0



I have attached a workbook, where in sheet1 it contains sample file and in sheet2 is what I expecting to get.


Attachment 4815

[craigwg]

I would recommend a loop. I'm not great at writing them but there is lots of documentation on the Interwebz. You should be able to loop through a range of cells. I've done this a few ways in the past, but I think that's your answer.

[GTO]

Greetings,

Try:

Option Explicit
    
Sub exa()
Dim rngData As Range
Dim rngCell As Range
Const FIRST_ROW As Long = 1 '<--- Change to suit
    
    With Sheet1 '<--- Using CodeName, or---> ThisWorkbook.Worksheets("Sheet1")
    
        Set rngData = .Range(.Cells(FIRST_ROW, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        
        For Each rngCell In rngData
            With rngCell
                If .Comment Is Nothing Then
                    .AddComment .Value
                    .ClearContents
                End If
            End With
        Next
    End With
End Sub

Hope that helps,

Mark

[rafi_07max]

Thanks for your help. Your codes work fine. But Your codes only works if the entire column has contents.
For e.g. Cell A1:A5 must be filled in order for the macro to work. If A1 to A3 is filled but A4 is empty cell and then A5-A7 if filled then the macro will have a error. What I mean is that there must not be any empty cell in between for your codes to work.
A sample picture of what i mean
Attachment 4820

How can I solve this problem?

[Kenneth Hobs]

Replace the IF code line.
[VBA] If .Comment Is Nothing And .Value <> Empty Then[/VBA]

[rafi_07max]

Thank alot Kenneth. It solved the problem.

Do take a look at one of my previous thread where u have helped

http://www.vbaexpress.com/forum/show...884#post228884

I having some problem in it. Do see whether u could provide any help. Thank You.