# 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

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

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?

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.

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

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

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

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.

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

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.