Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get value of a merged cell of an excel from its cell address in vba

How to get the value of a merged cell of an excel having range address like "$B$4:$B$11" in vba

like image 451
Sarika.S Avatar asked Feb 23 '12 05:02

Sarika.S


People also ask

Does VBA work with merged cells?

In VBA, there is a “MERGE” method that you can use to merge a range of cells or even multiple ranges into one. This method has an argument “Across” which is optional. If you specify TRUE it will merge each row in the range separately, and if you specify FALSE it will merge the entire range as one.

How do I pull a value from a cell in Excel VBA?

For the defined variable, put an equal sign and mention the cell address. Once again, put a dot to see the IntelliSense list. From the VBA IntelliSense list, choose “Value” property to get the value from the mentioned cell. Now the variable “CellValue” holds the value from the cell A1.

What is the cell reference of a merged cell?

In excel, merged cells are referred to as the address of the top left-most cell that was merged.


1 Answers

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

Hence, you can get the value of merged cells in range B4:B11 in several ways:

  • Range("B4").Value
  • Range("B4:B11").Cells(1).Value
  • Range("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.

like image 147
JMax Avatar answered Sep 20 '22 19:09

JMax