How to find if a cell is merged and read the value?

Question

How do I detect if a cell is merged?

If the cell is merged how do I read the value?

Answer 1

I don't think there's any formula to tell you if a cell is merged or not. You can write your own public vba function, put it in a code Module, and then use that on your sheet:

Function IsMerged(rCell As Range) As Boolean
' Returns true if referenced cell is Merged        
          IsMerged = rCell.MergeCells        
End Function

Then as an Excel formula to test cell A1:

=IsMerged(A1)

Answer 2

Here's how to read the cell's value in VBA, regardless if it is merged or not.

C.MergeArea.Cells(1, 1).Value

where C is the cell you want to look at. The way this works is that the MergeArea is either exactly the same as the cell itself if the cell is not merged; otherwise the MergeArea is the range of cells that have been merged. And when the cells are merged, the value is kept in the topleftmost cell.

Answer 3

Hurray! Figured out a way to check whether a cell is merged and return that cell's value:

Sub checkCellMerged1()
'With ThisWorkbook.ActiveSheet
Set ma = ActiveCell.MergeArea

On Error GoTo errHand
If ma = ActiveCell Then MsgBox ActiveCell.Address & " is not merged"
GoTo final

errHand:
If Err.Number = 13 Then MsgBox "Merged Address = " & ma.Address _
& vbCrLf & "Value = " & ma(1).Value

final:
On Error GoTo 0
'End With
End Sub