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?

Are you talking about finding this out in VBA or in the Excel interface? If the latter: Select the cell you're not sure about and look at the row/column headerins to see if multiple are highlighted. Or right click the cell and go to "format cells" - here in alignment see "merge" — Peter Albert, Jan 30 '13 at 08:52
Thanks for your answer: This is not what I am looking for: I just need a simple thing: Is the cell type is merged? If yes read the value else do nothing. For instance if the cell B2 is merged I want to read that value in C2, if it is not a merged cell then I do not want to read the value. — Ashikur Rahman, Jan 30 '13 at 09:23
Directly excel, but I cannot see merge property manually rather I need some function to check if the cell type is merged. — Ashikur Rahman, Jan 30 '13 at 09:34
Then use Vinny's answer below. E.g. `=IF(IsMerged(B2),C2,"")`will return you the value of C2 if B2 is merged. However, please note that C2 will return 0 if it is merged with B2 - even if it contained data before the merge! — Peter Albert, Jan 30 '13 at 09:42
Duplicate of [Looping through Merged cells in VBA](http://stackoverflow.com/questions/9391092/looping-through-merged-cells-in-vba) — brettdj, Jan 30 '13 at 09:44
Yes indeed. I think anything other than top-left in merge gets set to no value. I generally avoid merges if I possibly can. They can look nice, but cause trouble for programmers! — Vinny Roe, Jan 30 '13 at 09:45
also for simply editing a sheet, merged cells are a bit of an annoyance - e.g. when selecting a column and all surrounding columns are selected, too, because the header is merged. For the "nice looking part", quite often the text alignment "Center across selection" will do the same... — Peter Albert, Jan 30 '13 at 10:40

score 13 · Answer 1 · edited Jan 30 '13 at 10:53

13

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)

edited Jan 30 '13 at 10:53

brettdj

53,759
15
112
174

answered Jan 30 '13 at 09:38

Vinny Roe

903
4
8

score 6 · Answer 2 · answered Oct 10 '16 at 13:48

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.

score 1 · Answer 3 · answered Oct 10 '14 at 09:58

1

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

answered Oct 10 '14 at 09:58

ZAT

1,317
7
10

converting this into a function and you can basically get the value of any cell, be it merged or not. Thanks! – Mircea M Feb 19 '16 at 10:16

score 0 · Answer 4 · answered Apr 06 '15 at 15:03

0

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.

answered Apr 06 '15 at 15:03

Chris Puckett

1

score 0 · Answer 5 · edited Jun 05 '21 at 12:43

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

score 0 · Answer 6 · answered Nov 17 '21 at 16:06

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")

I have written a little SUDOKU sheet and I use a macro to test if a cell is merged or not.

If a cell is already merged it contains a found digit and I don't use it.

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

6 Answers6

Linked