How to save the value of merged cells in each cell?

Question

I created a sheet with merged cells, but the value of the folded cells is saved only in the first cell. In any case, in order to keep the same value in each of the cells, I need this for the formula that I use. Thanks!

Answer 1

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 macro

• Alt-F11 , Ctrl-R , Insert/Module menu, code insertion;
• : Alt-F8 , enter a new name ( UnmergeAndFill , for example), press Make or Create (? Do not know the English text)

Call the macro by pressing Alt-F8, select it, Run . Alternatively match it with a key

Answer 2

I know this is a rather old question, but this is the first place I landed while searching for an answer, and the accepted answer did not help. However, I found an excellent answer to MrExcel, which, it seemed to me, is worth putting on this topic in the interests of someone else looking for the answer to the question:
http://www.mrexcel.com/forum/general-excel-discussion-other-questions/487941-data-multiple-cells-within-merged-cell-possible.html

To keep the search link, the answer is remarkably simple; if you merge cells using Excel Format Painter rather than Merge Cells, it saves the “base” data / formulas of the merged cells. You just need to create a temporary merged block of cells in the correct format somewhere else to use it as a template for Format Painter. You can delete them later. However, one thing to keep in mind is that “hidden” data like this can be a trap for reckless, since editing a visible cell does not change the invisible ones.

Answer 3

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

I suppose in column A you merged cells (e.g. A1: A3 and A5: A8 merged).

• Insert a column before column A

• In type A1:

   =B1 

• Copy the formula below to A2:

   =IF(B2="",A1,B2) 

• Drag the formula u introduced in A2

Your formulas use the newly created column, and after use you can hide it.

Answer 4

You can use a custom VBA function that directly passes the value of the merged cell, regardless of which one you choose. In this case, there is no need to duplicate the values.

• Switch to VBA View (Alt-F11)
• Create a new module through Insert> Module
• In your project, go to the new module (you may want to assign a new name to it through the (name) property directly in the explorer)
• Copy the following code into the module (pasting code into ThisWorkbook will not work)

the 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 

5. Now you can use the formula in excel

the code:

 =GetMergedValue(A1) 

Where A1 is part of a merged cell.

Answer 5

Can you keep the actual values ​​elsewhere? instead of a merged cell? and yes, use the formula to display the value in the merged cell.

Answer 6

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

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

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

Note that this does not work for empty cells. First, fill in the empty cells in column "A" with something unique, for example, "(empty)", and replace it with a void of both "A" and "B" after filling in the column "B".

Answer 7

I improved the macro to make as many merged cells as you choose.

The 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 

Answer 8

  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