Pasting data into merged cells

[2022]

I have some data which I need to paste into column A of a file, using code.

For simplicity, let's assume that

cells A1 & B1 are merged

cells A2 and B2 are merged and

cells A3 and B3 are merged.

And let's say we have the value 1 in cell D2 and the value 2 in cell D3.

When I try top copy cells D2 and D3 then paste values only, into the merged A2&B2 and A3&B3 using code, I get an error!

If I do it manually, and try to paste values only, I get an error saying 'to do this, all merged cells need to be the same size'

But the source of data I'm copying the cells from is not merged data.

And I just want to copy the source data (which is a list of numbers in column A in a separate tab, but, in this case is the numbers 1 and 2 in cells D2 and D3, respectively, then paste it into column A, which has been merged with column B.

The merged columns are for ease of data input in other cells in those columns.

Here's some simple code I recorded, but the recording stopped at the point where it says 'to do this, all merged cells need to be the same size'

Sub MergedCells()
'
' MergedCells Macro
''
Range("D2:D3").Select
Selection.Copy
Range("A2:B2").Select

End Sub

Any thoughts would be greatly appreciated.

 

[ZMyrrh]

Do you have to loop through whole columns?

 

[Joe4]

Get rid of the merged cells. Seriously. They are a huge nuisance and will cause you nothing but trouble in Excel.
You can get the same visual effect without all the issues merged cells cause by using the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[ZMyrrh]

If not, this works

dim strr as string: strr="a2:b2"
Range("strr").Value = ActiveCell.Value & " " & ActiveCell.Offset(1, 0).Value

Make sure the activecell is on the top value that you want put in first. Also, select one cell only.

 

[rollis13]

This could be a solution but avoiding merged cells would be less painful.

Option Explicit
Sub MergedCells()
    '
    ' MergedCells Macro
    '
    Range("D2").Copy Range("A2").MergeArea
    Range("D3").Copy Range("A3").MergeArea
   
End Sub

PS. Hi to all.

 

[2022]

If not, this works

dim strr as string: strr="a2:b2"
Range("strr").Value = ActiveCell.Value & " " & ActiveCell.Offset(1, 0).Value

Make sure the activecell is on the top value that you want put in first. Also, select one cell only.

Thanks for your input - I tried this, but it didn't work. But the solution @rollis13 posted below did.....

Thanks for responding, though.

 

[2022]

Get rid of the merged cells. Seriously. They are a huge nuisance and will cause you nothing but trouble in Excel.
You can get the same visual effect without all the issues merged cells cause by using the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

That's good advice!

Thank you for sharing your experience!

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.