How Do I Combine Multiple Columns Into One Column?

gshanken

New Member
Joined
Jan 16, 2008
Messages
2
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This should do that.
Code:
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
 
Upvote 0
Hey Mike,

Thanks for the quick reply.

A few followups since I'm not very skilled w/VBA:

1) Are there any variables in the code that I need to change? (I see "Sheet 1" that I imagine I would change to the actual sheet name - is that correct - and are there any other specific changes I'd need to make?)

2) I usually create macros right through excel and then adjust the code through VBA when necessary. To create the macro directly in VBA, do I simply start from scratch and paste your code directly into it?

Thanks again!!

Greg


P.S. From a fellow head...nice tie-dye :)
 
Upvote 0
There's no need for special adjustments to that code.

Open the VBEditor, Insert a Module (not Class Module), create a sub and paste that into it.
 
Upvote 0
A really fast way to do this that requires no programming or even knowledge of how big your array is: save your excel file as a .txt (tab-delimited text). Open it in a text editor (on the Mac I use TextWrangler). Replace \t (tabs) with \r (carriage returns). (If you don't want to use those codes, you can just go to MS Word and type a tab, then copy and paste it into the search and replace dialogue box, then do the same for a carriage return.) Your data are now in a single column. You can open the .txt file in excel again.
 
Upvote 0
Mike, can you make this work by combining the data in a new worksheet?
Furthermore can all the data then be selected (as seen in Excel) for further editing.

This should do that.
Code:
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
 
Upvote 0
To move it to a different worksheet, change this line
Code:
ThisWorkbook.Sheets("SheetOther").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value

To get to the other sheet add

Code:
Application.Goto ThisWorkbook.Sheets("SheetOther").Range("A1")
 
Upvote 0
Thanks. It works.
How can I transfer the data into the first cell of the new worksheet, ie, cell A1.
 
Upvote 0
This should do that.
Code:
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

Many thanks indeed!

I wonder if following is possible...

I'd like to have following accomplished
Two columns
A --- B
C --- D - data from column C below data in column A and data from column D under column B
E --- F
G --- H
etc.
(column A,C,E,G are 'descriptions' whereas B,D,F,H etc. are expenses)

instead of
1 column
A
B
C
..

Thanks
=
 
Upvote 0
Try this

Code:
Sub test()
    Dim maxColumn As Long, i As Long
    
    maxColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Sheet1
        For i = 3 To maxColumn Step 2
            With Range(.Cells(Rows.Count, i).End(xlUp), .Cells(1, i + 1))
                Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 2).Value = .Value
            End With
        Next i
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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