Taking value of a range of merged cells

Question

I have a range of merged cells with values in them. I would simply like to read the value contained in each merged cell. The amount of cells in the merge area varies. Right now I have this:

Sub testnoms()
    Dim cell As Range
    For Each cell In ActiveSheet.Range("B20:K23") 'this would be the range to look at
        Debug.Print cell.MergeArea.Cells(1, 1).Value
    Next
End Sub

Right now it goes to every cell in the range and returns the value in its merged area. So I get the right values, but I have a duplicate for every cell in the merged area. For example, if 5 cells are merged together, it prints 5 times the value of the merged cell. Does anyone have an idea to fix this?

Tim Williams Tim Williams · Accepted Answer · 2015-06-17T16:13:55

Sub testnoms()
    Dim cell As Range
    For Each cell In ActiveSheet.Range("B20:K23") 
        If cell.Address() = cell.MergeArea.Cells(1).Address() Then
            Debug.Print cell.Address(), cell.MergeArea.Cells(1, 1).Value
        End If
    Next
End Sub

If you need to capture merged area values which may not be full-contained in your defined range:

Sub testnoms()
    Dim c As Range, d, addr
    Set d = CreateObject("scripting.dictionary")
    For Each c In ActiveSheet.Range("B20:K23")
        addr = c.MergeArea.Address()
        If Not d.exists(addr) Then
            d.Add addr, True
            Debug.Print c.Address(), c.MergeArea.Cells(1).Value
        End If
    Next
End Sub

Taking value of a range of merged cells

2 Answers