Arranging data from different Column

[Guest]

Hello,

I getting data in different columns, let say from Column A to Column Z. I
want to have all the data in Column A one below the other.

Currently I am cutting the data from column B to Column Z one by one and
pasting it in Column A one below the other. Is there any simpler way to
acheive this using macro. I am not proficient in macro coding. So could
someone provide me with the coding to acheive this.

Thanks in Advance

 

[Bill Kuunders]

copy the data in row 1
select cell a2
go to <edit> < paste special> and thick the transpose option (right hand
bottom corner)

or if you want to use the alt button
copy the data in row 1
select cell a2
hold <alt> button type ese and <enter>

ese )

Greetings from New Zealand

 

[Guest]

Thanks Bill for your reply.

But I have multiple column with data in multiple rows. It is not just one
row with multiple column.

 

[Bill Kuunders]

You can select more than one row at a time.
You can paste the transposed data anywhere.
It may be an option to paste them onto a new sheet.
Or send me an example of your data and I'll see what can be done with a
macro.

 

[Guest]

Send me a test mail to (e-mail address removed) and I will reply back with the
example file.

Thanks, Balaje

 

[Guest]

Bill,

Let say that we have data in different column like below.

AAA BBB CCC DDD EEE FFF
AAA BBB CCC DDD AAA
AAA CCC DDD
BBB DDD

Just want to have a code to which can bring all these data in columnA like
below

AAA
AAA
AAA
BBB
BBB
BBB
CCC
CCC
CCC
DDD
DDD
DDD
EEE
FFF
AAA
DDD


Thanks

 

[Bill Kuunders]

The macro below works for an area of 100 columns and 50 rows.
You can adjust this to suit.
Please note....It will clear all data in that area after copying values into
"A" column.

Sub arrange()
Dim counter As Integer
Dim i As Integer

counter = 0
Application.ScreenUpdating = False

For i = 2 To 100 ' 100 columns
to be adjusted

Cells(1, i).Resize(50).Copy '50 rows to be
adjusted
Range("a1").Select
ActiveCell.Offset(counter, 0).Activate
line1:
counter = counter + 1
ActiveCell.Offset(1, 0).Activate
If ActiveCell > 0 Then GoTo line1
Selection.PasteSpecial Paste:=xlValues
Next
Application.CutCopyMode = False
Cells(1, 2).Resize(50, 100).ClearContents '50 by 100 to be adjusted
Range("A1").Select

End Sub

Have fun
I assume you know how to store a macro and how to assign it to a text box
Greetings from New Zealand
Bill Kuunders

 

[Bill Kuunders]

Or a shorter version .
This one won't have problems if there are spaces in your original data
Regards
Bill K


Sub rearrange()

Dim i As Integer

counter = 0
Application.ScreenUpdating = False

For i = 2 To 100 ' 100 columns to be adjusted

Cells(1, i).Resize(50).Copy '50 rows to be adjusted
Range("a1").Select
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlValues
Next
Application.CutCopyMode = False
Cells(1, 2).Resize(50, 100).ClearContents '50 by 100 to be adjusted
Range("a1").Select
End Sub