# Thread: How to convert lots of columns of data all into just one column easily? Thanks: 0 Likes: 0

1. ## Re: How to convert lots of columns of data all into just one column easily? Originally Posted by smerrick Hello,

I do not think I have been clear with my request. As a simple example, imagine C3:C7 has random data. In addition, there is other random data in cells D3:D7, plus E3:E7 and so on. What I would like to do in column A is to have the data from C3:C7 into A3:A7, the data from D3:D7 into A8:A12 (i.e. underneath), the data form E3:E7 into A13:A17 and so on.

Is this at all doable?

Thanks
Not if you have 2495 rows by 622 columns, it's too much to fit into 1 column  Reply With Quote

2. ## Re: How to convert lots of columns of data all into just one column easily?

If I understood well OP has 5 rows for each 624 columns (result 3120 rows) but maybe I am wrong   Reply With Quote

3. ## Re: How to convert lots of columns of data all into just one column easily?

Hi,

Yes the example you have (aimded at Sandy666) is exactly what I am trying to find the solution for, thank you for taking the time to look into it for me.

With the code, I simply copied it when recording a macro but it does not seem to work for me?  Reply With Quote

4. ## Re: How to convert lots of columns of data all into just one column easily? Originally Posted by sandy666 If I understood well OP has 5 rows for each 624 columns (result 3120 rows) but maybe I am wrong I'm going by
data starts at row 6 and finishes at to 2,500.  Reply With Quote

5. ## Re: How to convert lots of columns of data all into just one column easily?

as mentioned in post #3 this is PowerQuery (Get&Transform) NOT vba  Reply With Quote

6. ## Re: How to convert lots of columns of data all into just one column easily? Originally Posted by Fluff I'm going by
right

good description is a half success   Reply With Quote

7. ## Re: How to convert lots of columns of data all into just one column easily?

OK thanks. I have not come across this feature before, is there an alternative way as opposed to using this? Originally Posted by sandy666 as mentioned in post #3 this is PowerQuery (Get&Transform) NOT vba  Reply With Quote

8. ## Re: How to convert lots of columns of data all into just one column easily?

As others have said, it sounds like you have too much data to put into one column but you are welcome to give this a try:

Code:
```Sub combineCols()
Dim r As Range
Dim a(), b()
Dim lastRow As Long, totalValues As Long, i As Long, j As Long, k As Long

lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set r = Range("A6", .Cells(lastRow, "WZ"))
totalValues = Application.CountA(r)
If totalValues > Rows.Count Then
MsgBox "Can't be done. Not enough rows!!!", vbCritical
Else
k = 0
ReDim b(1 To totalValues, 1 To 1)
For j = 1 To UBound(a, 2)
For i = 1 To UBound(a)
If LenB(a(i, j)) Then
k = k + 1
b(k, 1) = a(i, j)
End If
Next j
Next j
Erase a
Erase b
End If
Set r = Nothing
End Sub```
edit: You could export to a csv I suppose, but excel wouldn't open it.  Reply With Quote

9. ## Re: How to convert lots of columns of data all into just one column easily? Originally Posted by smerrick is there an alternative way as opposed to using this?
I don't know, maybe someone else  Reply With Quote

10. ## Re: How to convert lots of columns of data all into just one column easily?

Thanks for the code. I have tried this but get 'compile error invalid or unqualified reference' at the .cells part of the code? Originally Posted by trunten As others have said, it sounds like you have too much data to put into one column but you are welcome to give this a try:

Code:
```Sub combineCols()
Dim r As Range
Dim a(), b()
Dim lastRow As Long, totalValues As Long, i As Long, j As Long, k As Long

lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set r = Range("A6", .Cells(lastRow, "WZ"))
totalValues = Application.CountA(r)
If totalValues > Rows.Count Then
MsgBox "Can't be done. Not enough rows!!!", vbCritical
Else
k = 0
ReDim b(1 To totalValues, 1 To 1)
For j = 1 To UBound(a, 2)
For i = 1 To UBound(a)
If LenB(a(i, j)) Then
k = k + 1
b(k, 1) = a(i, j)
End If
Next j
Next j
Erase a
Erase b
End If
Set r = Nothing
End Sub```
edit: You could export to a csv I suppose, but excel wouldn't open it.  Reply With Quote

## User Tag List

#### Tags for this Thread

amount, column, columns, data, kind, power query #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•