I want to transpose a loop of 5 columns and 100 rows into 1 row

I have 5 columns and 100 rows of information I need to put into a single horizontal row.

for example:

red green blue yellow red green blue yellow red green blue yellow ect...

to

red green blue yello开发者_如何学运维w red green blue yellow red green blue yellow...ect

please help I have no idea how to create a macro so be as basic as possible! thank you

---

Here is some code that does a transpose on the same sheet. Change the rows and column as required. Hopefully you know how to open the VBA editor and run the macro.

Sub transpose()
Dim row
Dim targetRow
Dim targetColumn
Dim column
Dim columnMin
Dim columnMax
Dim rowMin
Dim rowMax
Dim tmp

rowMin = 1
rowMax = 2
columnMin = 1
columnMax = 5

targetColumn = 7
targetRow = 1

row = rowMin
While row <= rowMax

    column = columnMin

    While column <= columnMax

        Cells(row, column).Select
        tmp = ActiveCell.Value
        Cells(targetRow, targetColumn).Select
        ActiveCell.Value = tmp
        targetRow = targetRow + 1
        column = column + 1

    Wend

    row = row + 1

Wend

End Sub

What this does is loops over each row that is required. Start at the first column that is required. Loops over each column in that row, get the value and put in the target row and column. Then increment the target row.

I'm hoping there is enough example code here that you can make modifications yourself to solve your specific problem.

'edit Damn. Just noticed that question asked for 1 row, but this does 1 column. Shouldn't be too hard to change.