VBA Excel Export Rows to Columns in .txt

Question

Does anyone have an idea how to write VBA in Excel to export multiple rows to just one column. Additionally, I would like to add "enter" at the end of each "row". Ideally, this would be exported in .txt, but it would be already great even if it is converted inside the same document.

Thank you!

Edit: For example: I have rows which contain some text/value A1:A5, B1:B5, ... I would need all of These rows to get "moved" in a single column D for instance. So, A1 would go to D1,A2-D2, A3-D3 ... B1 to D7 and so on. After each end (A5, B5, C5,...) I would need an empty cell (when I convert this to .txt it means enter). I hope it is a bit clearer now.

Could you please be more specific about out your question? For example, illustrate which format in you Excel, and which format should be exported in .txt — Paul, Jun 08 '17 at 05:54
Possible duplicate of [Excel - Combine multiple columns into one column](https://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column) — YowE3K, Jun 08 '17 at 06:40

score 0 · Answer 1 · answered Jun 08 '17 at 06:38

you need add reference Microsoft ActiveX Data Objects 2.8 Library

Set ff = CreateObject("ADODB.Stream")
ff.Type = 2
ff.Charset = "utf-8"
ff.Open

For Each Cell In Range("A1:D5").Cells
ff.WriteText Cell.Value & vbNewLine
Next

path_to_save = ActiveWorkbook.Path

ff.SaveToFile path_to_save & "\test.txt", 2

score 0 · Accepted Answer · answered Jun 08 '17 at 06:55

The code below will do what you describe. Use the constants in the beginning to set how many columns and rows that should be moved to one single column, specified by TargetColNo. An empty cell will be added after each scanned column.

If you prefer to save it to a textfile you can use this code and add a text file to add the result in instead of a column.

Sub Rows2OneColumn()
    Const StartColumnNo = 1 ' Start at column A
    Const EndColNo = 3      ' End at column C
    Const StartRowNo = 1    ' Start at row 1
    Const EndRowNo = 5      ' End at row 5

    Const TargetColNo = 5   ' Put result in column E

    Dim source_row As Integer
    Dim source_col As Integer
    Dim target_row As Integer

    target_row = 1

    For source_col = StartColumnNo To EndColNo
        For source_row = StartRowNo To EndRowNo
            Cells(target_row, TargetColNo).Value = Cells(source_row, source_col).Value
            target_row = target_row + 1
        Next
        target_row = target_row + 1 ' leave one cell empty
    Next
End Sub

VBA Excel Export Rows to Columns in .txt

2 Answers2