Extract value from merged cells in two or more rows

[akalien]

Hi,

I have a sheet with merged and non merged cells in column C and I want to extract the value from the merged cells into two or more other cells in column D corresponding to the size of the merged cell.

Value 001 into D2 and D3
Value 008 into D4, D5, D6, D7 and D8
...
If there is a non merged cell the value from column C should also be copied to column D.

Many thanks in advance for your help.
AK

 

[Tupe77]

This is written on a cell phone, so I haven’t tested this.

Select from column range where merged cells are.

sub TS_CopyMerge()
dim SrcRNG as range, Cell as range
Set SrcRNG=Selection

For Each Cell In SrcRNG
        cell.address.offset(0,1).value=Cell.MergeArea(1).Value
Next
end sub