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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
If I understood well OP has 5 rows for each 624 columns (result 3120 rows) but maybe I am wrong :devilish:
 
Last edited:

smerrick

Active Member
Joined
Feb 10, 2009
Messages
255
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?
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
as mentioned in post #3 this is PowerQuery (Get&Transform) NOT vba
 

smerrick

Active Member
Joined
Feb 10, 2009
Messages
255
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

Active Member
Joined
Jul 26, 2011
Messages
479
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
        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.
 
Last edited:

smerrick

Active Member
Joined
Feb 10, 2009
Messages
255
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:

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
        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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,634
Messages
5,488,025
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top