Transpose data in single column into 4 columns

[Closed]
Report
Posts
1
Registration date
Thursday August 27, 2015
Status
Member
Last seen
August 27, 2015
-
Posts
1934
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
July 21, 2021
-
Hello,

I have data in a single column and would like to transpose it into a row for every 4 rows. I have about 7000 rows in the 1 column.

I'd like to go from
A
B
C
D
E
F
G
H
I
J etc (I have about 7000 rows)

into

A B C D
E F G H
I J K L etc.


I used this macro but it stops after around 30 rows.

Thank you in advance!

Sub test()
Dim rng As Range, m As Integer, c As Range
Columns("c:E").Delete
m = 4
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
Set c = Range("a2")
Do While c <> ""
'MsgBox c.Address

Range(c, c.Offset(m - 1, 0)).Copy
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Set c = c.Offset(m, 0)
Loop


End Sub


1 reply

Posts
1934
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
July 21, 2021
144
monty1992, Good morning.

I tested your macro in a column with 50,000 rows of data.
It worked perfectly.

Did you repeat your test more often and at all times he stopped after 30 lines?

Remember:
Your macro only works while the cells are different from empty.
You put this condition in the Do While statement.

Check your data.
Locate the first three consecutive empty cells in the column.
The value before it should be the last occurrence in your new table.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!