Copy / Paste - Kind of Transpose

  • Thread starter Thread starter Carl Jarvis
  • Start date Start date
C

Carl Jarvis

Hello Group, I wonder if you can help me.

I have a load of data in Column A, spearated by a space, for example...

1
2
3
4

1
2
3

1
2
3
4
5

1
2
3
4

I want to bring it all toghether so that it looks like this...

1111
2222
3333
4 44
5

Do you know a way to do this? Maybe using the empty row as the divide /
break?

Many thanks to those who help.

Carl.
 
Hi Carl,

Try this code below.

Sub Macro1()
Dim x As Integer
Dim y As Integer
Dim z As Integer

For x = 1 To 4
Range("A" & x).Select
ActiveCell.FormulaR1C1 = x
Next x

For y = 6 To 9
Range("A" & y).Select
ActiveCell.FormulaR1C1 = y - 5
Next y

For z = 11 To 14
Range("A" & z).Select
ActiveCell.FormulaR1C1 = z - 10
Next z

End Sub

Sub Macro2()

Dim t As Integer
Dim s1 As Integer
Dim s2 As Integer
Dim s3 As Integer

For s1 = 6 To 15 Step 5
Range("A" & s1).Select
Selection.Cut
For t = 2 To 3
Cells(1, t).Select
Next t
ActiveSheet.Paste
Next s1

For s2 = 7 To 16 Step 5
Range("A" & s2).Select
Selection.Cut
For t = 2 To 3
Cells(2, t).Select
Next t
ActiveSheet.Paste
Next s2

For s3 = 8 To 17 Step 5
Range("A" & s3).Select
Selection.Cut
For t = 2 To 3
Cells(3, t).Select
Next t
ActiveSheet.Paste
Next s3

For s4 = 9 To 18 Step 5
Range("A" & s4).Select
Selection.Cut
For t = 2 To 3
Cells(4, t).Select
Next t
ActiveSheet.Paste
Next s4
End Sub
 
Back
Top