How can I update text in a merged cell with VBA?

Question

Suppose I have 2 cells that are merged, lets say Sheet1!$A$1:$B$1.

And lets say there's a piece of text that's centered across both cells. How do I programmatically update that value with VBA? I have a simple user form with a button, on button click, I want the cell to update.

I've tried grabbing the Range, but that doesn't seem to work.

Answer 1

In addition to the other answers, it's worth noting that you can also use MergeArea to update via either of the cells:

Range("A1").MergeArea.Value="New value"
Range("B1").MergeArea.Value="New value"

If the cell isn't merged then it will just ignore the MergeArea (ie. it treats it like a merged area with one cell...)

Answer 2

Use this function if you need to reference a cell that might or might not be part of a merged range:

Function Reference_cell_even_if_merged(Cell_Range As Range) As Range
If Cell_Range.MergeCells Then
    Set Reference_cell_even_if_merged = Cell_Range.MergeArea
Else
    Set Reference_cell_even_if_merged = Cell_Range
End If
End Function

Answer 3

If you have merged the cells, then you can only edit the value of the merged cells by referencing the upper left most cell.

So, if A1 and B1 are merged, the only way to change the date is:

Range("A1").value = "data"

Or

Cells(1,1).Value = "data"

Calling Range("B1").Value = "data" will not change the visible text.