Multiple columns data to single column

mlee3

Board Regular
Joined
May 6, 2008
Messages
70
I have data on 10 colmns. How to make all 10 columns data to single column only? i.e. append 10 columns data to only one column.

Appreciate any help!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You're going to have to provide a bit more information here, as there are many ways this could be perceived.

Let's say you have data in A1:A10, B1:B10, C1:C10, etc. for 10 columns. Do you want the data in B1:B10 to be added below A1:A10? Then C1:C10 below the new A1:A20? It won't be difficult to do, but it's better to know ahead of time rather than code and re-code due to lack of planning.

For the heck of it, I've coded an example of my "assumption" above, which would combine all columns into column A, stacked one data set below the other.
Code:
Sub combine()
    Dim i As Long, lastRow As Long
    For i = 2 To 10
        lastRow = [A65536].End(xlUp).Row
        Range(Cells(1, i), Cells(Cells(65536, i).End(xlUp).Row, i)).Cut _
            Range("A" & lastRow).Offset(1, 0)
    Next i
End Sub
 
Last edited:
Upvote 0
mvptomlinson,

Thank you so much! Your assumption is exactly what I need. I tried the codes and it worked perfectly!
 
Upvote 0
You're going to have to provide a bit more information here, as there are many ways this could be perceived.

Let's say you have data in A1:A10, B1:B10, C1:C10, etc. for 10 columns. Do you want the data in B1:B10 to be added below A1:A10? Then C1:C10 below the new A1:A20? It won't be difficult to do, but it's better to know ahead of time rather than code and re-code due to lack of planning.

For the heck of it, I've coded an example of my "assumption" above, which would combine all columns into column A, stacked one data set below the other.
Code:
Sub combine()
    Dim i As Long, lastRow As Long
    For i = 2 To 10
        lastRow = [A65536].End(xlUp).Row
        Range(Cells(1, i), Cells(Cells(65536, i).End(xlUp).Row, i)).Cut _
            Range("A" & lastRow).Offset(1, 0)
    Next i
End Sub

I am doing this now but I want to stack the columns in another workbook. Is there anyway to do this?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top