vba 从vba中的单元格地址获取excel合并单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9407474/
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
Get value of a merged cell of an excel from its cell address in vba
提问by Sarika.S
How to get the value of a merged cell of an excel having range address like "$B$4:$B$11" in vba
如何在vba中获取范围地址如“$B$4:$B$11”的excel的合并单元格的值
回答by JMax
Even if it is really discouraged to use merge cells in Excel (use Center Across Selectionfor instance if needed), the cell that "contains" the value is the one on the top left(at least, that's a way to express it).
即使真的不鼓励在 Excel 中使用合并单元格（Center Across Selection例如，在需要时使用），“包含”值的单元格是左上角的单元格（至少，这是一种表达方式）。
Hence, you can get the value of merged cells in range B4:B11in several ways:
因此，您可以通过B4:B11多种方式获取范围内合并单元格的值：
Range("B4").ValueRange("B4:B11").Cells(1).ValueRange("B4:B11").Cells(1,1).Value
Range("B4").ValueRange("B4:B11").Cells(1).ValueRange("B4:B11").Cells(1,1).Value
You can also note that all the other cells have no value in them. While debugging, you can see that the value is empty.
您还可以注意到，所有其他单元格中都没有任何值。在调试时，您可以看到该值为empty。
Also note that Range("B4:B11").Valuewon't work (raises an execution error number 13 if you try to Debug.Printit) because it returns an array.
另请注意，这Range("B4:B11").Value将不起作用（如果您尝试这样Debug.Print做，则会引发执行错误编号 13 ），因为它返回一个数组。
回答by todd w
Josh Brown gave (in a comment) what I think is the best answer:
乔什·布朗（在评论中）给出了我认为最好的答案：
When I don't know the bounds of the merged area, I get the value with
当我不知道合并区域的边界时，我得到的值
Range("B6").MergeArea.Cells(1,1).Value
This is helpful in VBA when, for example, you are looping through files that might have merged cells of unknown ranges, so you can be much more general with this method. Thanks Josh!
这在 VBA 中很有用，例如，当您循环浏览可能合并未知范围单元格的文件时，因此您可以使用此方法更通用。谢谢乔希！
回答by Christopher Lazok
This can be done in 2 steps:
这可以通过 2 个步骤完成：
First name the range of the merged cells; highlight the merged cell then go on the Ribbon Bar: Formulas Tab --> Define Name;
首先命名合并单元格的范围；突出显示合并的单元格，然后转到功能区栏：Formulas Tab --> Define Name;
Make sure there are no spaces in the name. Example: defined_Name. Go to the desired cell you wish to see the output/result. In that cell, type: =defined_Name.
确保名称中没有空格。例子：defined_Name。转到您希望查看输出/结果的所需单元格。在该单元格中，键入：=defined_Name。
Press enter because your done.
按回车键，因为你已经完成了。
