Previous Topic Next Topic

Copy values in row to columns in spaces

Solved/Closed
LearnVBA - Sep 13, 2014 at 08:05 AM
 LearnVBA - Sep 16, 2014 at 01:54 AM
Hello,

I am a beginner in VBA, and would mcuh appreciate some guidance on transposing data in a row to a column via VBA.

My row data is as follows:

Column A
Datapoint1
Datapoint2
Datapoint3
Datapoint4

I can simply copy and paste (transpose), but the result will be like this.
Column A, Column B Column C
Datapoint1 Datapoint2 Datapoint3

What I am trying to achieve is
Column A, Column B, Column C, Column D, Column E, Column F, Column G
Datapoint1, empty, empty, empty, empty, empty, Datapoint 2

Datapoint 3 would appear in Column M. There are 5 columns in between.

There are about 3,000 Datapoints that need to be transposed into column (separated by 5 columns in between).

Your help would be much appreciated.

Thank you,




3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 13, 2014 at 09:39 AM
try this macro and see sheet2 

Sub test()
Dim r As Range, c As Range
Dim r2 As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
With Worksheets("sheet2")
Set r2 = .Range("A1")
End With

For Each c In r
c.Copy r2.Offset(0, 6)
With Worksheets
Set r2 = r2.Offset(0, 6)
End With
Next c
With Worksheets("sheet2")
Range(.Range("A1"), .Range("a1").Offset(0, 5)).EntireColumn.Delete
End With
End Sub
0
VBALearn
Sep 13, 2014 at 08:40 PM
Thank you very much, this works miraculously.
I have first copy and pasted the formulae, and it works. I am reading line by line to interpret them and revise to the worksheet format I am working on.

Thanks very much, I envy this skill set you have...
0
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 14, 2014 at 01:08 AM
you are welcome
0
Answer 3 / 3
LearnVBA
Sep 16, 2014 at 01:54 AM
I just came back to say thank you again. I am using this code again today.
I have been reading and understanding line by line. This will be a very helpful tool for me in many occasion. I will try to also support this site by participating (once I have build some knowledge).

thank you,
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Excel function question
1234ah -
ac3mark -

3 responses