1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by nagovind, Feb 8, 2019.

  1. nagovind

    nagovind Member

    Messages:
    299
    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
  2. AliGW

    AliGW Active Member

    Messages:
    332
    Don't use merged cells - they are more trouble than they are worth.
  3. AlanSidman

    AlanSidman Active Member

    Messages:
    446
  4. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    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 (vb):
    Sub ConverToValue()
    [mergedCellName].Value = [mergedCellName].Value
    End Sub
     
    This works for a merged region just as it does for a single cell.
  6. nagovind

    nagovind Member

    Messages:
    299
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    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 (vb):
    Sub UnmergeTargetRange
        [targetRange].UnMerge
    End Sub
    … or you can simply click the 'Merge and Center' ribbon button.

Share This Page