Count rows for merged cells

Question

I have a problem to count the number of rows for each block of merged cells, in Excel.

I have a value on A1. If I merge cells A1 to A4 the value appears centered, on the range A1-A4. Then I have another value in A5. If I merge cells A5 to A12, this second value appears centered on this second block of cells.

What I want is to count number of rows for each block of merged cells.

I have tried to use VBA programming to detect these number of rows, with function "MergeArea" and "CurrentRegion.Count" but the program detects that the two blocks are contiguous and counts 12 rows, instead of 4 and then 8. If it detects "4" first, I could put the correct instruction on a loop and then detect "8".

Answer 1

There are several downsides to merged cells in terms of VBA but here is a simple method to try.

My sheet looks like this:

Code:

Sub CountMergedRows()
    For i = 1 To 20
        RowCount = Range("A" & i).MergeArea.Rows.Count
        
        If RowCount > 1 Then
            MsgBox ("Cell [A" & i & "] has " & RowCount & " merged rows")
            i = i + RowCount - 1
        End If
    Next i
End Sub

---

Results are two message boxes that appear like this:

Answer 2

Method Range("A" & i).MergeArea.Rows.Count suggested by Portland Runner works fine, however the function has slightly incorrect logic as it is missed that Next also increments i, so it is more correct to write:

Sub CountMergedRows()
    For i = 1 To 20
        RowCount = Range("A" & i).MergeArea.Rows.Count

        If RowCount > 1 Then
            MsgBox ("Cell [A" & i & "] has " & RowCount & " merged rows")
            i = i + RowCount - 1 'note -1 here
        End If

    Next i
End Sub