Excel Macros: From Table to Column

Question

I have an Excel table with multiple rows and columns like this:

1 2 3
4 5 6
7 8 9

I need to write macros, which will transform all my data in one single column and will place it in sheet2

1
2
3
4
5
6
7
8
9

Answer 1

I've found a formula in this site that do what you want: http://www.cpearson.com/excel/TableToColumn.aspx

Answer 2

Sub Macro1()

    Dim StartTable As Range     ' The original table of data
    Dim RNdx As Long            ' Row index
    Dim CNdx As Long            ' Column index

    Dim TabeRowIndex As Long        ' Row number of Range("StartTable")
    Dim TableColumnIndex As Long    ' Column number of Range("StartTable")
    Dim N As Long               ' Index into ColumnData
    Dim WS As Worksheet         ' Worksheet reference


    '''''''''''''''''''''''''''
    ' Initialize the variables.
    '''''''''''''''''''''''''''
    Set ColumnData = Range("ColumnData")
    Set StartTable = Range("StartTable")
    TabeRowIndex = Range("StartTable").Row
    TableColumnIndex = Range("StartTable").Column
    Set WS = Worksheets("Sheet1")
    N = 0

    ''''''''''''''''''''''''''''''''''''''
    ' Loop across then down filling
    ' cells with element N of ColumnData.
    ''''''''''''''''''''''''''''''''''''''
    For RNdx = TabeRowIndex To (TabeRowIndex + (StartTable.Rows.Count))
        For CNdx = TableColumnIndex To TableColumnIndex + StartTable.Columns.Count - 1
            N = N + 1
            ColumnData.Cells(N, 1) = WS.Cells(RNdx, CNdx).Value
        Next CNdx
    Next RNdx

End Sub

Answer 3

Alternative answer by using a formula in sheet2:
(You column count is 3 here - you can change it)

=INDIRECT(CONCATENATE("'Sheet1'!"; ADDRESS(FLOOR(ROW()-1; 3)/3+1; MOD(ROW()-1; 3)+1)))