How to get the value of a merged cell of an excel having range address like "$B$4:$B$11" in vba
如何获得合并后的excel单元格的值，该单元格的范围地址在vba中为“$B$4:$B$11”
3 个解决方案
#1
35
Even if it is really discouraged to use merge cells in Excel (use Center Across Selection for 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)，“包含”该值的单元格也是左上角的单元格(至少，这是一种表示该值的方式)。
Hence, you can get the value of merged cells in range B4:B11 in several ways:
因此，您可以从以下几个方面获得合并单元格在B4:B11范围中的值:
Range("B4").Value- 范围(B4)value
Range("B4:B11").Cells(1).Value- 范围(B4:B11).Cells(1)value
Range("B4:B11").Cells(1,1).Value- 范围(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.
您还可以注意到，所有其他单元格都没有任何价值。在调试时，可以看到该值为空。
Also note that Range("B4:B11").Value won't work (raises an execution error number 13 if you try to Debug.Print it) because it returns an array.
还要注意,范围(“B4:B11”)。值不会起作用(如果您尝试调试，则会引发执行错误编号13)。打印它)因为它返回一个数组。
#2
20
Josh Brown gave (in a comment) what I think is the best answer:
乔什·布朗(Josh Brown)在评论中给出了我认为最好的答案:
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中很有用，例如，当您循环遍历可能合并了未知范围的单元格的文件时，因此您可以更一般地使用这个方法。谢谢杰克!
#3
0
This can be done in 2 steps:
这可以通过两个步骤完成:
First name the range of the merged cells; highlight the merged cell then go on the Ribbon Bar: Formulas Tab --> Define Name;
第一个名称合并单元格的范围;选中合并后的单元格，然后在Ribbon栏上:公式选项卡—>定义名称;
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.
按enter键是因为你完成了。
#1
35
Even if it is really discouraged to use merge cells in Excel (use Center Across Selection for 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)，“包含”该值的单元格也是左上角的单元格(至少，这是一种表示该值的方式)。
Hence, you can get the value of merged cells in range B4:B11 in several ways:
因此，您可以从以下几个方面获得合并单元格在B4:B11范围中的值:
Range("B4").Value- 范围(B4)value
Range("B4:B11").Cells(1).Value- 范围(B4:B11).Cells(1)value
Range("B4:B11").Cells(1,1).Value- 范围(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.
您还可以注意到，所有其他单元格都没有任何价值。在调试时，可以看到该值为空。
Also note that Range("B4:B11").Value won't work (raises an execution error number 13 if you try to Debug.Print it) because it returns an array.
还要注意,范围(“B4:B11”)。值不会起作用(如果您尝试调试，则会引发执行错误编号13)。打印它)因为它返回一个数组。
#2
20
Josh Brown gave (in a comment) what I think is the best answer:
乔什·布朗(Josh Brown)在评论中给出了我认为最好的答案:
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中很有用，例如，当您循环遍历可能合并了未知范围的单元格的文件时，因此您可以更一般地使用这个方法。谢谢杰克!
#3
0
This can be done in 2 steps:
这可以通过两个步骤完成:
First name the range of the merged cells; highlight the merged cell then go on the Ribbon Bar: Formulas Tab --> Define Name;
第一个名称合并单元格的范围;选中合并后的单元格，然后在Ribbon栏上:公式选项卡—>定义名称;
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.
按enter键是因为你完成了。