vba 计算合并单元格的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22185267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count rows for merged cells
提问by Ivanna
I have a problem to count the number of rows for each block of merged cells, in Excel.
我在计算 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.
我在 A1 上有一个值。如果我将单元格 A1 合并到 A4，则该值会在 A1-A4 范围内居中显示。然后我在 A5 中有另一个值。如果我将单元格 A5 合并到 A12，则第二个值将出现在第二个单元格块的中心。
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".
我曾尝试使用 VBA 编程来检测这些行数，使用函数“MergeArea”和“CurrentRegion.Count”，但程序检测到这两个块是连续的并计数 12 行，而不是 4 行，然后是 8 行。如果检测到首先是“4”，我可以将正确的指令放在一个循环中，然后检测“8”。
回答by Portland Runner
There are several downsides to merged cells in terms of VBA but here is a simple method to try.
就 VBA 而言，合并单元格有几个缺点，但这里有一个简单的方法可以尝试。
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
End If
Next i
End Sub
Results are two message boxes that appear like this:
结果是两个消息框，如下所示：
回答by bnd
Method Range("A" & i).MergeArea.Rows.Countsuggested by Portland Runner works fine, however the function has slightly incorrect logic as it is missed that Nextalso increments i, so it is more correct to write:
Range("A" & i).MergeArea.Rows.CountPortland Runner 建议的方法工作正常，但是该函数的逻辑略有错误，因为它Next也会增加i，因此编写更正确：
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
