Looping through comments on an excel workbook in vba with merged cells

Question

I have a workbook, with multiple sheets, which have comments. I have to loop through each of the sheets and pick up the comments. I have implemented the following logic.

For Each Ip_Sheet In ActiveWorkbook.Worksheets
    Set Rng = Ip_Sheet.Cells.SpecialCells(xlCellTypeComments)
    If Rng Is Nothing Then
        MsgBox "No comments in the sheet"
    Else
        For Each cell In Rng
            Comment_Author_NameAndComment = Split(cell.Comment.Text, ":")
            AuthName = Comment_Author_NameAndComment(0)
            AuthComments = Comment_Author_NameAndComment(1)

The above logic works fine if there are no merged cells in the worksheet. However, if there are merged cells/rows, the loop For Each cell In Rng runs for each of the cells in the merged cells range. For example, if columns A:D are merged, then the loop runs for each of the cells A, B, C and D and I get the same value in the AuthName and AuthComments variables.

My question is, how do I make the loop to skip to the next comment on the worksheet if I find a merged cell?

Edit: I also tried to loop through all the comments in the sheet by the following method, however, the method was not successful - the Rng.Comment object was always empty.

        For Each cmnt_obj In Rng.Comment
            cmt_txt = cmnt_obj.Text
        Next cmnt_obj

Possible duplicate of Detect merged cells in VBA Excel with MergeArea — braX
@brax that's not a dup. While its good info on detecting merged ranges, It doesn't address how to solve this problem — chris neilsen
@Deepak you could report the comment if it's either not part of a merged range, or it's the top left cell of a merged range (ie cell.address = cell.mergerange.cells(1).address) — chris neilsen
@chrisneilsen Am sorry, I could not understand what you mean with your above comment. — Deepak V
@braX This is not a duplicate. I am aware of how to detect if a cell is part of merged cells or not using cell.MergeCells = True. However, what I would like to do is, if I know that this cell has a comment and is part of a merged cell, then I would like to iterate to the next cell having a comment. — Deepak V

chris neilsen chris neilsen · Accepted Answer · 2019-08-25T21:28:12

Since SpecialCells(xlCellTypeComments) returns all cells for a Merged Range, you need to detect when a cell is part of a named range and only process one of those cells. You can use Range.MergeCells to detect a merged cell, and Range.MergeArea to return the merged range itself. Then only report the comment if the cell is the Top Left cell of the merged range.

Something like this:

Sub Demo()
    Dim rng As Range
    Dim cl As Range
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        Set rng = ws.Cells.SpecialCells(xlCellTypeComments)
        If Not rng Is Nothing Then
            For Each cl In rng.Cells
                If cl.MergeCells Then
                    If cl.Address = cl.MergeArea.Cells(1).Address Then
                        ReportComment cl
                    End If
                Else
                    ReportComment cl
                End If
            Next
        End If
    Next
End Sub

Sub ReportComment(cl As Range)
    Dim Comment_Author_NameAndComment() As String
    Dim AuthName As String
    Dim AuthComments As String

    Comment_Author_NameAndComment = Split(cl.Comment.Text, ":")
    AuthName = Comment_Author_NameAndComment(0)
    AuthComments = Comment_Author_NameAndComment(1)
    Debug.Print AuthName, AuthComments
    '...
End Sub

Looping through comments on an excel workbook in vba with merged cells

1 Answers