How to keep value of merged cells in each cell?

Question

I created a sheet with merged cells, but the value of the merged cells is only stored in the first cell. Is there anyway, to keep the same value in each of the cells, I need that for a formula I use. Thanks!

openoffice, gnumeric, excel, csv, lotus, koffice, google docs, Mac: numbers, tables, mesa...? — sehe, Jun 24 '11 at 06:37
maybe the question isn't cleas, so again, i merged 3 cells in my sheet, but the value of the merged cells resides only in the first cell. I want that each of the cells must have the same value yet be displayed as one merged cell. — Kinshuk, Jun 24 '11 at 06:47
your sheet? your sheet of paper? Stem cells? You didn't tell us whether you are using a computer, and if so, which one. Next we need to know what application you are using — sehe, Jun 24 '11 at 06:49

score 7 · Accepted Answer · answered Jun 24 '11 at 08:11

7

In Excel 2003 this macro does the job:

Public Sub UnmergeAndFill()
    With Selection
        If .MergeCells Then
            .MergeCells = False
            Selection.Cells(1, 1).Copy
            ActiveSheet.Paste 'Or PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    End With
End Sub

Create the macro by

pressing Alt-F11, Ctrl-R, menu Insert/Module, paste the code;
alternatively: Alt-F8, type a new name (UnmergeAndFill, e.g.), click Make or Create (? don't know the English button text)

Invoke the Macro by pressing Alt-F8, select it, Run. Alternatively map it to a key

answered Jun 24 '11 at 08:11

sehe

374,641
47
450
633

1

this umerges them... i dont actually want to unmerge them, i want them to stay merged, yet each cell should have the content that is visible in the merged cells... – Kinshuk Jun 24 '11 at 09:12
2

@Kinshuk: in that case, you're out luck. The application object model of Excel explicitely does not allow that. – sehe Jun 24 '11 at 09:55
1

Stop using merged cells (you can achieve a similar effect with protected cells, hidden cells, protect sheet; Next, disable display of grid lines, and use cell borders to display the grid you want shown) – sehe Jun 24 '11 at 09:58
A different answer below by @Knackiedoo seems to achieve exactly what Kinshuk was looking for originally. Would be interesting if someone could elaborate on how this is coherent with Excel's application object model. – Clang May 06 '20 at 11:58

score 6 · Answer 2 · answered May 23 '16 at 08:41

You could use a custom VBA function that gives directly the value of the merged cell, no matter which one you select. In that case it is not necessary to duplicate the values.

Switch to VBA view (Alt-F11)
Create a new module via Insert > Module
In your project, browse to the new module (you might want to give it a new name via the (name) property just under the explorer)
Copy the following code in the module (pasting the code in ThisWorkbook will not work)

Code:

Option Explicit

Function GetMergedValue(location As Range)
    If location.MergeCells = True Then
        GetMergedValue = location.MergeArea(1, 1)
    Else
        GetMergedValue = location
    End If
End Function

You can now use the formula in excel

Code:

=GetMergedValue(A1)

Where A1 is a part of a merged cell.

This is by far the easiest and most efficient way of doing it. — Iorek, Apr 22 '20 at 16:43

score 5 · Answer 3 · answered Dec 04 '14 at 21:09

I know this is a rather old question, but this is the first place I landed when looking for an answer, and the accepted answer did not help at all. I DID discover an excellent answer, however, on MrExcel which I thought was worth putting on this thread for the benefit of anyone else googling for an answer:
http://www.mrexcel.com/forum/general-excel-discussion-other-questions/487941-data-multiple-cells-within-merged-cell-possible.html

To save looking up the link, the answer is remarkably simple; if you merge cells using Excel Format Painter, rather than Merge Cells, it preserves the data/formulae 'underlying' the merged cells. You just need to create a temporary merged block of cells in the right format somewhere else, to use as a template for the Format Painter. You can delete them afterwards. One thing to watch out for, though, is having 'hidden' data like this can be a trap for the unwary, since editing the visible cell does not change the invisible ones.

It seems like this is exactly what @Kinshuk was looking for originally. Certainly helped me a lot, thank you! — Clang, May 06 '20 at 11:55

score 2 · Answer 4 · edited Sep 28 '12 at 11:49

2

you can make a new column (or row) and apply this formula in first cell and drag it down:

I suppose in column A you have merged cells (for example A1:A3 & A5:A8 are merged).

Insert a column before column A
In A1 type:
```
=B1
```
Copy the formula below in A2 :
```
=IF(B2="",A1,B2)
```
Drag down the formula u typed in A2

In your formulas use the newly created column and after use you can hide it.

edited Sep 28 '12 at 11:49

Mihai Iorga

39,330
16
106
107

answered Aug 21 '12 at 08:02

Nima

21
1

plavozont · Answer 5 · 2016-05-12T02:35:26.860

Assume column "A" has merged cells - put this in B1 and copy it to fill the rest of the column:

=IF(ISBLANK(A1);OFFSET(B1;-1;0);A1)

It checks if the cell to the left has a value if it has it returns its value, if not, it takes the value from the upper cell.

Note that it doesn't work for the empty cells. First fill empty cells in column "A" with something unique, like "(empty)" and replace it back with emptiness both in "A" and "B" after filling column "B".

score 0 · Answer 6 · answered Apr 04 '18 at 18:12

I improved on sehe's macro to do as many merged cells as you select.

Code:

Public Sub UnmergeAndFillMultiple()
    Dim w As Range, m As Range, n As Range
    For Each w In Selection.Cells
        If w.MergeCells Then
            Set n = w.MergeArea.Cells(1, 1)
            Set m = w.MergeArea
            w.MergeCells = False
            n.Copy
            m.PasteSpecial
        End If
    Next
End Sub

score 0 · Answer 7 · answered Jun 24 '11 at 06:37

0

can you not store the actual values somewhere else? instead of the merged cell? and yes , use a formula to display the value on the merged cell.

answered Jun 24 '11 at 06:37

DAEMYO

989
8
12

well maybe i didn't make myself clear, what i meant was, i merged 3 cells in my sheet, but the value of the merged cells resides only in the first cell. I want that each of the cells must have the same value yet be displayed as one merged cell. and, no i cannot store them in a different place – Kinshuk Jun 24 '11 at 06:47

score 0 · Answer 8 · edited Jun 03 '22 at 17:18

0

Say A13 through A52 are merged. a = Cells(13,1) will return a value but a = Cells(14,1) will not "Empty"
Fix:
a = Cells(X,1).MergeArea(1, 1) 'if x=13 through 52 will return value in A13.
don't know how i got this to work but it works.

edited Jun 03 '22 at 17:18

rootShiv

1,375
2
6
21

answered May 25 '22 at 02:40

James Cottrell

1

score -1 · Answer 9 · edited Apr 25 '19 at 13:24

-1

 Dim rowcnt As Long, i As Long
    rowcnt = Cells(Rows.Count, "A").End(xlUp).Row
    For i = rowcnt To 3 Step -1
        With Cells(i, 1)
            If .Value = Cells(i - 1, 1).Value Then
                .Font.ColorIndex = 9
            End If
        End With
    Next i

edited Apr 25 '19 at 13:24

ush189

1,342
6
22
30

answered Apr 25 '19 at 12:38

Balaji Aerospian

1

How to keep value of merged cells in each cell?

9 Answers9

Linked