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

Question

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

Contemptuous

Excel, I assume? Worth adding as a tag... – Boman

Range("B4:B11").Cells(1).Value ? – Sericin

Answer 1

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.

Furr

When I don't know the bounds of the merged area, I get the value with Range("B6").MergeArea.Cells(1,1).Value – Affright

Nice tip! <!--5 more to go...--> – Furr

Also: Range("B4:B11").Item(1).Value , or simply, Range("B4:B11")(1).Value – Demirelief

Answer 2

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!

Awad

Answer 3

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;

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.

Press enter because your done.

Googins

This does not pertain to VBA. Also, using VBA you might have no idea where the merged cell is or what it's bounds are. – Galina

Answer 4

(Excel 2016)

I am hoping this will help somebody.

I have found that if you press delete on a merged cell, the target.cells.count = the number of merged cells.

Whereas if you change the value on the same cell, the target.cells.count = 1

This caused me issues as I was skipping multi selections in my worksheet_change code by testing the number of cells selected.

Nausea