Excel VBA code to copy a Range and paste it within a cell

**rshnkmr39** · 11-17-2014, 01:47 AM

Hi,

I have a few numbers in the range A1:A10
125465
876562
684653
313647
251684
322151
351654
321654
846841
515421

I need to copy this range and paste all of it in cell B1,
it must look like how it is displayed above but within cell B1

Thank you so much in advance

**:) Sixthsense :)** · 11-17-2014, 02:08 AM

Sub GetRangeValueInCell()
    Range("B1").Value = Join(Application.Transpose(Range("A1:A10")), vbCrLf)
End Sub

**jolivanes** · 11-17-2014, 02:40 AM

If you get little squares at the end of each value, change the vbCrLf to Chr(10)

Following works also but is not near as neat as

Sixthsense

's code

Change the cell references as required
This assumes your values are in Column B starting at B1 and you want the result in Cell G1

Sub Try()
Dim i As Long
    Application.ScreenUpdating = False
    Range("G1").WrapText = True
    Range("G1").Value = Cells(1, 2).Value
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Range("G1").Value = Range("G1").Value & Chr(10) & Cells(i, 2).Value
    Next i
    Application.ScreenUpdating = False
End Sub