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

i have 5 columns , 100 rows of information need put single horizontal row.

for example:

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

to

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

please have no idea how create macro basic possible! thank you

here code transpose on same sheet. change rows , column required. know how open vba editor , run 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 loops on each row required. start @ first column required. loops on each column in row, value , put in target row , column. increment target row.

i'm hoping there enough example code here can make modifications solve specific problem.

'edit damn. noticed question asked 1 row, 1 column. shouldn't hard change.