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

Answer 4

A common need is to target only the first cell in a merged range. This code does that.

The If statement result is only true for the first cell; merged or not.

Sub RunOnlyForFirstCell()
    Dim c As Range
    Dim MergedCellsArea As Range
    
    For Each c In Selection.Cells
        Set MergedCellsArea = c.MergeArea
        If c.Address = MergedCellsArea(1, 1).Address Then
            '''run your sub
            Debug.Print c.Address; Selection.Cells.Count; MergedCellsArea(1, 1).Address
        End If
    Next c
 End Sub

Answer 5

The following code is answering to both questions

Function GetValue(iRow As Integer, iCol As Integer) As String
    Dim rCell As Range
    Set rCell = oSheet.Cells(iRow, iCol)
        
    sText = ""
            
    If Not rCell.MergeCells Then
        sText = rCell.Value
    End If
        
    GetValue = sText
End Function
...
Set oSheet = Worksheets("Sheet1")

Using Excel, I have written a little SUDOKU sheet and I use a VBA macro to test if a range of cells has been merged or not.

In following example (above image), I use following code to detect empty Sudoku cells and try to resolve unfound digit.

If a cell (Excel 3x3 range) is merged it contains a found digit.

'**********************************************************************
'* HighlightFoundCells()
'*-------------------------------------------------
'* When a Sudoku Cell (3x3 Excel range) contains only 1 small digit,
'* it is displayed using bigger font in middle of merged 3x3 Excel range
'**********************************************************************

For n = 1 To 9
    For i = 1 To 9
        iCount = 0
        For j = 1 To 9
            If GetDigitValue(n, i, j) = n Then
                iCount = iCount + 1
                jLast = j
            End If
        Next j
        If iCount = 1 Then
            Call MergeCells(iFirstRow + 3 * (i - 1), iFirstCol + 3 * (jLast - 1))
            ActiveCell.FormulaR1C1 = CStr(n)
            Call HighlightCell(iLast, j)
        End If
    Next i
Next n

'**********************************************************************
'* GetDigitValue()
'*---------------------------------------------------------------------
'* Get digit value contained in merged 3x3 cell's range
'* that represent a found digit.
'**********************************************************************

Function GetDigitValue(n As Integer, iRow9 As Integer, iCol9 As Integer) As Integer
    row = iFirstRow + (iRow9 - 1) * 3 + (n - 1) \ 3
    col = iFirstCol + (iCol9 - 1) * 3 + (n - 1) Mod 3
    
    Dim rCell As Range
    Set rCell = oSheet.Cells(row, col)
    
    Dim c As Integer: c = 0
        
    If Not rCell.MergeCells Then
        c = rCell.Value
    End If
    
    GetDigitValue = CInt(c)
End Function

'**********************************************************************
'* HighlightCell()
'*---------------------------------------------------------------------
'* Change background of 3x3 group of cells that contains a found digit
'**********************************************************************

Sub HighlightCell(i As Integer, j As Integer)
    nRow = iFirstRow + (i - 1) * 3
    nCol = iFirstCol + (j - 1) * 3
    oSheet.Range(Cells(nRow, nCol), Cells(nRow + 2, nCol + 2)).Select
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Excel code to merge a range of cells is following

Sub MergeCells(iRow As Integer, iCol As Integer)
    Range(oSheet.Cells(iRow, iCol), oSheet.Cells(iRow + 2, iCol + 2)).Select
    Selection.ClearContents
    'Range("X8").Select
    'ActiveCell.FormulaR1C1 = "5"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Merge
    With Selection.Font
        .Name = "Arial"
        .Size = 48
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

Answer 6

If B and C are always populated there is a simple non-VBA method to determine if a cell is merged. Simply do COUNTA(B2,C2) and check the total. If B2 is merged with C2, the total will be 1, if it's not the count will be 2.