CODEWITHSUNDEEP
vbaexcel
David G
David G

Reputation: 2347

Taking value of a range of merged cells

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?

Upvotes: 1

Views: 879

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

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

Upvotes: 4

BruceWayne
BruceWayne

Reputation: 23283

Instead of using the range B20:K23, use B20:B23. Using the B:K Range, it will (as you noticed) go through each 'underlying' cell in that merged cell and return the same value over and over. If you use B:B instead, it'll act as you wanted it to - go through each merged cell ONCE and return that value.

Does that make sense?

edit: Please let me know why downvotes, I am also trying to learn, so if this method is not a good one, kindly let me know why so I can learn too.

Upvotes: -1

Related Questions