• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Paste Only Values in Merged Cells

nagovind

Member
Kindly advise is there is a way to copy the data from the merged cells that contains a formula and paste in the same cell only the values

Thank you
 

vletm

Excel Ninja
nagovind
If You still would like to do that then ...
Select that merged cell,
copy with Ctrl+C and
after that use PasteSpecial with values-option.
... as You've written Yourself.
 

Peter Bartholomew

Well-Known Member
I suspect that the idea that merged cells represent a great evil, though representing received developer wisdom, depends very much on one's use case. After all, only a small proportion of sheets contain formulas (<5%) whereas over 50% contain merged cells.

My personal acceptance of merged cells (in particular to create form-like interfaces) stems from the fact that I do not employ the usual interactive practices of spreadsheet development which are built upon manipulation of the grid. For me, the sheet is simply a blank canvas and it is the objects (tables, named ranges, array formulas, pivot tables, charts … ) that I address by name. Their actual location is an irrelevancy determined by journalistic and graphic design considerations.

For coding purposes, I believe the address of a merged region is the address of its top-left cell but, then again, I never use cell coordinates to addresses the worksheet from VBA; I find range names and table names far more reliable.

The VBA required to convert a formula to its value is the somewhat odd looking line of code:

Code:
Sub ConverToValue()
[mergedCellName].Value = [mergedCellName].Value
End Sub
This works for a merged region just as it does for a single cell.
 

Peter Bartholomew

Well-Known Member
I read from the OP that the goal was replace a formula in a merged cell by its value.

To copy a value from a merged cell to a single cell manually then use 'copy' followed by 'Paste Value'.

To unmerge the cells from a macro but leave the string in place
Code:
Sub UnmergeTargetRange
    [targetRange].UnMerge
End Sub
… or you can simply click the 'Merge and Center' ribbon button.
 
Top