Formatting Problem while copying data

[princ_wns]

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]

You can unmerge cells after pasting data at destination.

Rajan

[Admin]

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


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]

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


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


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


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


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


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.