Converting merged cell's content to Note or Comment

Question

idea is to convert merged cell's content to note or comment. i have found this piece of code

'Convert Cell Content to Comment
Sub ConvertToComment()
Dim C As Range
For Each C In Selection
    C.ClearComments
    If Len(C.Value) > 0 Then
        C.AddComment
        C.Comment.Text C.Value & ""
    End If
    'Optional: Delete cell content after converting
    C.ClearContents
    Next C
End Sub

but this doesn't work correctly with merged cells. when applied there is an error 400 pops out. please, help out to fix it?

Answer 1

I could be wrong but I believe your issue is with the C.ClearContents line.

Maybe try this ...

'Convert Cell Content to Comment
Sub ConvertToComment()
    Dim C As Range
    
    For Each C In Selection
        C.ClearComments
        
        If Len(C.Value) > 0 Then
            C.AddComment
            C.Comment.Text C.Value & ""
        End If
        
        'Optional: Delete cell content after converting
        C.Value = ""
    Next C
End Sub

... it has basically the same effect and should do the job.