Read Value from a merged Range (Excel)

I have a column whose header is a range of B1:C1. the data is more like

+----+----+

| Header |

+----+开发者_StackOverflow----+

| H2 | H3 |

+----+----+

| 1 | 2 |

| 3 | 4 |

+----+----+

I have a variable named rng such as

Set rng = Range("B1:C1")

now using rng variable, I want to select value "H3" or "2", or "4". I used the following syntax

rng.Offset(1,1).value

and isntead of giving me "H3" it gave me value from next colum i.e d2.

There is not a straight forward way to fix this unexpected behaviour. My understanding is that, regarding merged cells, the reference for the offset is based on the whole of the cell and that the merged cells are treated as one cell instead of many cells.

In your instance, if Range("B1:C1") is merged then the next column (i.e Offset(0,1)) is column D. Excel views the merged range as single cell and so from a visual standpoint the next column along is column D and not column C. This can be confusing in my view.

The best way to work around this is to avoid using merged cells as headers but instead use Centre Across Selection formatting:

1) De-merge Range("B1:C1")

2) Select Range("B1:C1") > Format > Cells

3) In Horizontal dialog box select 'Center Across Selection'

If you do that then the following code will work:

Sub GetOffsets()
    Dim rng As Range
    Set rng = Range("B1")

    Debug.Print rng.Offset(1, 0) // H2
    Debug.Print rng.Offset(2, 0) // 1
    Debug.Print rng.Offset(3, 0) // 3
    Debug.Print rng.Offset(1, 1) // H3
    Debug.Print rng.Offset(2, 1) // 2
    Debug.Print rng.Offset(3, 1) // 4
End Sub

If you really need to use merged cells, Excel has this "issue" when it comes to offsets from merged cells. In your case above, it does make sense that cell D2 becomes offset(1,1) from B1.

One solution is to use nested offsets:

You have your variable:

Set rng = Range("B1:C1")

Then use the following adjustment to your code:

rng.Offset(1,0).offset(0,1).value

This way, you first offset by row, to move down to B2, then offset by column to get to C2.