计算合并单元格的行 [英] Count rows for merged cells

问题描述

在Excel中计算合并单元格的每个块的行数时遇到问题.

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

我在A1上有一个值.如果我合并单元格A1到A4，则该值将居中显示在范围A1-A4上.然后我在A5中还有另一个值.如果我将单元格A5合并到A12，则第二个值将集中在第二个单元格的中心.

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.

我尝试使用VBA编程通过功能"MergeArea"和"CurrentRegion.Count"来检测这些行数，但是程序检测到两个块是连续的，并计数12行，而不是4行，然后是8行.如果它首先检测到"4"，则可以将正确的指令放在循环中，然后检测到"8".

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".

推荐答案

就VBA而言，合并单元有很多缺点，但这是一种简单的尝试方法.

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

我的工作表如下:

代码:

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
        End If
    Next i
End Sub

---

结果是两个显示如下的消息框:

---

Results are two message boxes that appear like this:

这篇关于计算合并单元格的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！