Offset merged cells

[Rolly_Sefu]

Hello.

I have the following table:

Column B is a merged cell, ( 4 rows merged into once cell)

I am trying to get the value 2 from column A by and value 4 from column A by offsetting the merged cell of column B

x = Range("M8").MergeArea.Offset(1, -1)

M8 = my merged cell

so I would need to get the values for L9 and L11

But this does not give me a good result, always returns the value as a merged cell.

Any ideas ?

Thanks.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

There is a reason that one avoids merged cells.

If M8:M11 are merged, this ungainly expression returns the array of values from L9:L12.
Note that the Offset has been broken into two parts.

With Range("M8")
    With .Offset(0, -1).Resize(.MergeArea.Rows.Count, 1)
    
        MsgBox Join(Application.Transpose(.Offset(1, 0).Value))
        
    End With
End With

 

[Rolly_Sefu]

Hello.

Thanks for the reply.

I managed to make this line: "Range(Range("M8").Offset(0, -1).Address).Offset(1, 0)"

Witch one for the 2 versions mine or yours is better ?

Thanks.

 

[mikerickson]

Which works? Which do you understand?
That's the better one.