Formatting Problem while copying data

**princ_wns** · 04-03-2012, 02:56 PM

Hi All,

How to copy data of merged cells into a cell. How it can be done from VBA.Please respond.

Thanks in Advance...

**Rajan_Verma** · 04-03-2012, 03:26 PM

You can unmerge cells after pasting data at destination.

Rajan

**Admin** · 04-03-2012, 04:35 PM

Hi

Your question is not clear to me. Merged cells always create headache. Better to unmerge them and fill the blank cells with the above data.

Something like

Code:

Sub kTest()
    
    Dim rngRange    As Range
    
    Set rngRange = Range("a2:b10")       '<< adjust to suit
    
    With rngRange
        .UnMerge
        On Error Resume Next
        .SpecialCells(4).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
    
End Sub

**Rick Rothstein** · 04-03-2012, 07:18 PM

Originally Posted by princ_wns

How to copy data of merged cells into a cell. How it can be done from VBA.

Your question is kind of vague and lacking in details, but see if this code line addresses your problem. Let's say your merged cells are C2:F3. You can reference that range either in its entirety or by an one or more set of cells within it for this method. So, you could use any one of the following to copy just the value in that merged range to another cell (for my example, I used cell D8 for the destination cell)...

Code:

Range("D8").Value = Range("C2:F3")(1).MergeArea.Value

Code:

Range("D8").Value = Range("E2:D3")(1).MergeArea.Value

Code:

Range("D8").Value = Range("C2")(1).MergeArea.Value

Code:

Range("D8").Value = Range("F3")(1).MergeArea.Value

Code:

Range("D8").Value = Range("D3")(1).MergeArea.Value

All of the above copy just the value in the merged cells C2:F3 into the cell D8. So, using the setup I show above, any cell or cells in the merged area may be used to transfer the value in the merged area to another cell.