How Do I Combine Multiple Columns Into One Column?

[gshanken]

I currently many columns of data that I want in one single column and am trying to automate the process with a macro.

I don't want to combine/consolidate the data. I want to copy the data from Col B and place it under the last cell of data in Col A, then copy the data from Col C and place it under the last cell of data Col A (which now also has the original Col B data underneath the original Col A data), then from Col D to Col A, etc... until all of the data is in Col A.

The length of each column will be changing on a daily basis, i.e. each column will have a different # of rows.

Does anyone know of way to make this happen?

Any help is greatly appreciated!

Thanks,

Greg

 

[mrwul62]

Thanks! I am afraid I get an error on the 'MaxColumn' line.

This one is okay.. (that is the 1st macro)



The last macro showed an error





In the above example the idea is to:
1) either do it in multiple steps, i.e.
select the data in column C and D - run the macro - which would them move the data
from column C to column A
from column D to column B

select the data in column E and F - run the macro - which would them move the data
from column C to column A
from column D to column B


etc. i.e. each time a block of 2 columns

or in 1 step
select data cells in columns C uptil (in this case) column H
run the macro
then data ex C, E, G to move into column A
and data ex D, F, H to move into column B

right now this requires quite some copy pasting...

or.. if it is too complicated what about
first selecting column A, C, E, G - run a macro that combines the data into 1 column (A)
then secondly run the same macro on columns B, D, F, H that combines the data in column B.

Thanks.

 

[mikerickson]

What error is being generated? What sheet are you doing this on?

 

[mrwul62]

The error is marked in yellow in the screenshot above.

 

[mikerickson]

THat is the line that is causing the error. What is the error message that accompanied it?

 

[mrwul62]

Sorry for the late response.
You mean the the pop-up ?
It says something like 'Error 424 Object required'

Is it that what you meant?

 

[mikerickson]

Yes, that;s what I mean and if that line is causing that error, then your workbook doesn't have a sheet whose codename is Sheet1. Change that to match your situation and the error should go away.

 

[mrwul62]

Am very sorry, still the same error.
Probably I am doing something wrong, but honestly I wouldn't know what.
FWIW: I am using the Dutch version of Excel 2016 and using Office 2016 Language Preference tool to
launch it using the English GUI.
Don't know whether that has something to do with it though.

 

[iazeemkhan]

Dear Sir,

Greetings....

I have same problem, I run below VBA code, but its taking Heading also in the list. I only want to make list of items under each heading.

Code is fantastic, only its taking heading also in the list which i dont want in it.

Please advice Sir.

This should do that.

Dim oneColumnHead As Range
Dim columnHeads As Range

With ThisWorkbook.Sheets("sheet1")
    Set columnHeads = Range(.Cells(1, 2), .Cells(1, .Columns.Count).End(xlToLeft))
End With

For Each oneColumnHead In columnHeads
    With oneColumnHead.EntireColumn
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value
        End With
    End With
Next oneColumnHead