How to convert lots of columns of data all into just one column easily?

[smerrick]

Hello,

Thanks for reading.

I have columns of data from column C right through to column WZZ. What I want to do is to put all of this data into column A only. Each column has the exact same amount of rows - data starts at row 6 and finishes at to 2,500.

Is there a qucik way to do this?


Kind Regards

 

[Fluff]

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

 

[sandy666]

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

 

[smerrick]

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?

 

[Fluff]

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.

 

[sandy666]

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

 

[sandy666]

I'm going by

right

good description is a half success

 

[smerrick]

OK thanks. I have not come across this feature before, is there an alternative way as opposed to using this?

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

 

[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:

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
        Workbooks.Add.Sheets(1).Range("A1").Resize(totalValues).Value = b
        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.

 

[sandy666]

is there an alternative way as opposed to using this?

I don't know, maybe someone else

 

[smerrick]

Thanks for the code. I have tried this but get 'compile error invalid or unqualified reference' at the .cells part of the code?

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:

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
        Workbooks.Add.Sheets(1).Range("A1").Resize(totalValues).Value = b
        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.